﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;

namespace CommunitySystem.BO.EmpbasicBO
{
    public class EmpbasicBO
    {
        public byte[] ExcelToBytes(HSSFWorkbook workbook)
        {
            byte[] buffer;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.ToArray();
                ms.Close();
            }

            return buffer;
        }
        public string GetDengLuJueSe(string loginName)
        {
            string sql = string.Format(@"select RoleName from Roles where ID = 
(select RoleID from UserInfo where LoginName = '{0}')", loginName);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return ds.Tables[0].Rows[0]["RoleName"].ToString();
        }

        public string GetDengLuOrg(string loginName)
        {
            string sql = string.Format(@"select OrgCode from UserInfo where LoginName = '{0}'", loginName);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return ds.Tables[0].Rows[0]["OrgCode"].ToString();
        }

        #region 员工基本信息
        public ObservableCollection<DiaoDongModel> GetEmp_BasicInfoModelByCardNo(string cardno, int orgid)
        {
            ObservableCollection<DiaoDongModel> rights = new ObservableCollection<DiaoDongModel>();
            string sql = @"SELECT emp.id EmpbasicID,emp.name Name,emp.cardno CardNo,emp.Gender ,emp.Birthday,emp.orgid OrgID,ed.JobDate ZWCCSJ,ed.RXRZWSXSJ ZWSXSJ,
o.name OrgName,emp.PositionName,ed.DutyNo DutyName,ed.DutyAttribute,ed.DutyLevel from Emp_BasicInfo emp inner join orgforhr o on emp.orgid = o.id
left join emp_duty ed on emp.id = ed.empbasicid and JobStatus = '在任' where emp.EmpStatus = '在职' ";
            if (orgid != 0)
                sql += " and emp.orgid = " + orgid;
            if (!string.IsNullOrEmpty(cardno))
                sql += " and emp.cardno = '" + cardno + "'";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(DiaoDongModel.FillEntity(dr));
            }
            return rights;
        }

        /// <summary>
        /// 查询总共多少条数据yy
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="EmpID"></param>
        /// <param name="name"></param>
        /// <param name="orgtype"></param>
        /// <param name="highQueryCondition">高级查询条件，xf修改</param>
        /// <returns></returns>
        public int GetEmp_BasicInfoCounts(string OrgID, string EmpID, string name, string orgtype, string empstatus, string highQueryCondition, bool keji, bool chuji)
        {
            string sql = "";
            //用于查看员工列表
            if (EmpID == "0")
                //用于列表上面的搜索
                if (string.IsNullOrEmpty(name))
                {
                    sql = @"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 

{0}
where EmpStatus = '" + empstatus + @"' and 1=1 ";
                    if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                    {
                        sql += " and (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) ";
                    }
                    if (!string.IsNullOrEmpty(orgtype) && orgtype != "组织机构")
                    {
                        sql = string.Format(@"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 
{0}
where EmpStatus = '" + empstatus + @"' and 1=1 
  and o.Type = '{0}'", orgtype);
                    }
                }
                //姓名输入框有文字
                else
                {
                    sql =
                        @"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 
--left join OrgForHR o on emp.orgid=o.ID 
{0}
where  EmpStatus = '" + empstatus + @"' and 1=1 and  (emp.Name like '%" +
                        name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + "%')";
                    if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                    {
                        sql += "  and (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) ";
                    }
                    if (!string.IsNullOrEmpty(orgtype) && orgtype != "组织机构")
                    {
                        sql =
                            @"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 

{0}
where  EmpStatus = '" + empstatus + @"' and 1=1 and  (emp.Name like '%" +
                            name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + "%')" + " and o.Type = '" + orgtype + "'";
                    }
                }

            //用于查看单个员工
            else sql = @"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM Emp_BasicInfo emp 
{0}
where emp.ID in  EmpID";
            if (!string.IsNullOrEmpty(highQueryCondition))
            {
                string condition = @"
inner join OrgForHR o on emp.orgid=o.ID 
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID";
                sql = string.Format(sql, condition);
                sql += " and " + highQueryCondition;
            }
            else
            {
                sql = string.Format(sql, " inner join OrgForHR o on emp.orgid=o.ID   ");
            }
            if (keji == true && chuji == false)
                sql += " and emp.kechuji = 0";
            if (chuji == true && keji == false)
                sql += " and emp.kechuji = 1";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }


        /// <summary>
        /// yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="EmpID"></param>
        /// <param name="name"></param>
        /// <param name="orgtype"></param> 
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="highQueryCondition">高级查询条件，xf修改</param>
        /// <returns></returns>
        public ObservableCollection<Emp_BasicInfoModel> GetEmp_BasicInfos(string OrgID, string EmpID, string name, string orgtype, int start, int end,
            string empstatus, string highQueryCondition, bool? keji, bool? chuji)
        {
            string iszgxl = "true";
            if (highQueryCondition.Contains("edu.Professional") && !highQueryCondition.Contains("edu.EducationName"))
                iszgxl= "false";
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql = "";
            string sql11 = "";
            //用于查看员工列表
            if (EmpID == "0")
                //用于列表上面的搜索
                if (string.IsNullOrEmpty(name))
                {
                    if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                    {
                        #region
                        sql = @" row_number()over(order by a.orgID ) Sequence,a.* from ( SELECT distinct  emp.[ID]
           ,emp.[Name],[CardNo] ,[Gender] ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday] ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
     -- ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end ,[BirthPlace],NativePlace,[Native],[Political] ,[Health],case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName ,PositionName,[RegisterNo] ,[OrganizationType],case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
,Password,SID,JCGZNX
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions],kechuji
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
      ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation
  FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
";
                        sql += iszgxl == "true" ? " left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID"
    : "left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID";

                        sql += @"
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
      left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
 left join
(select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,IsTopEducation from Emp_BasicInfo emp 
"; sql += iszgxl == "true" ? @"
left join
(select * from Emp_Education where IsTopEducation = 1) edu on emp.ID = edu.EmpBasicID
" : @"left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
where 1=1 and " + highQueryCondition + ""; sql += @"
        ) ee on ee.ID = emp.ID 
where EmpStatus = '" + empstatus + @"' 
                        and 1=1 {0} and  (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + "))) a  where  1=1";
                        //                      --and  emp.ID not   in   
                        //--  (select   top " + start + @" emp.ID from   Emp_BasicInfo emp left join OrgForHR o on o.ID = emp.OrgID
                        //    --  where  EmpStatus = '" + empstatus + @"' and (ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")))  and  (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + "))
                        #endregion
                        #region
                        sql11 = @" row_number()over(order by a.orgID ) Sequence from ( SELECT distinct  emp.[ID]
           ,emp.[Name],[CardNo] ,[Gender] ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday] ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
     -- ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end,[BirthPlace],NativePlace,[Native],[Political] ,[Health],case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName ,PositionName,[RegisterNo] ,[OrganizationType],case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
,Password,SID,JCGZNX
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions],kechuji
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
      ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation
  FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
";
                        sql11 += iszgxl == "true" ? " left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID"
    : "left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID";

                        sql11 += @"
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
       left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
 left join
(select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,IsTopEducation from Emp_BasicInfo emp
"; sql11 += iszgxl == "true" ? @"
left join
(select * from Emp_Education where IsTopEducation = 1) edu on emp.ID = edu.EmpBasicID
" : @"left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
where 1=1 and " + highQueryCondition + ""; sql11 += @"
        ) ee on ee.ID = emp.ID 
where EmpStatus = '" + empstatus + @"' 
                        and 1=1 {0} and  (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + "))) a  where  1=1";
                        //                      --and  emp.ID not   in   
                        //--  (select   top " + start + @" emp.ID from   Emp_BasicInfo emp left join OrgForHR o on o.ID = emp.OrgID
                        //    --  where  EmpStatus = '" + empstatus + @"' and (ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")))  and  (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + "))
                        #endregion
                    }
                    else
                    {
                        #region
                        //top " + (end - start) + @"
                        sql = @"  row_number()over(order by a.orgID ) Sequence,a.* from ( SELECT distinct  emp.[ID]
     ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
     -- ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end
      ,[BirthPlace]
      ,NativePlace
      ,[Native]
,Password,SID,JCGZNX
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions],kechuji
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
        ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation 
FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID

left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
";
                        sql += iszgxl == "true" ? " left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID"
    : "left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID";

  sql+= @"
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
 left join
(select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,IsTopEducation from Emp_BasicInfo emp 
"; sql+=iszgxl == "true" ? @"
left join
(select * from Emp_Education where IsTopEducation = 1) edu on emp.ID = edu.EmpBasicID
" : @"left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
where 1=1 and " + highQueryCondition+""; sql+= @"
) ee on ee.ID = emp.ID where EmpStatus = '" + empstatus + @"' 

and 1=1 {0}) a  where  1=1";
                        #endregion
                        #region
                        //top " + (end - start) + @"
  sql11 = @"  row_number()over(order by a.orgID ) Sequence from ( SELECT distinct  emp.[ID]
     ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
     -- ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end
      ,[BirthPlace]
      ,NativePlace
,Password,SID,JCGZNX
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions],kechuji
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
        ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation 
FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID

left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
"; sql11 += iszgxl == "true" ? " left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID"
     : "left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID";
  sql11 += @"
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
 left join
(select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,IsTopEducation from Emp_BasicInfo emp 
"; sql11 += iszgxl == "true" ? @"
left join
(select * from Emp_Education where IsTopEducation = 1) edu on emp.ID = edu.EmpBasicID
" : @"left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
where 1=1 and " + highQueryCondition + ""; sql11+=@"
) ee on ee.ID = emp.ID where EmpStatus = '" + empstatus + @"' 

and 1=1 {0}) a  where  1=1";
                        #endregion
                    }


                }
                //姓名输入框有文字
                else
                {
                    if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                    {
                        #region

                        sql = string.Format(@" row_number()over(order by a.orgID ) Sequence,a.* from ( SELECT distinct  emp.[ID]
                                            ,emp.[Name] ,[CardNo],[Gender],kechuji
                                            ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
                                            ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
                                          --  ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end
,[BirthPlace] ,NativePlace,[Native] ,[Political],[Health],Password,SID,JCGZNX
                                            ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
                                            ,OrgID,o.Name OrgName ,PositionName ,[RegisterNo],[OrganizationType]
                                            ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
                                            ,HuoDeFangShi ,HouseholdRegister ,[Marriage] ,[HomeAddress] ,[Phone],[EmpIdentity]
                                            ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
                                            ,[Special] ,[TechnicalPositions] ,emp.HasPhoto , emp.PhotoName ,[PersonnelType]
                                            ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
                                            ,'false' as IsSelected
                                            ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
                                            ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
                                            else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
                                            case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
                                            ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
                                            ee.EducationName,ee.EducationType
                                            ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
                                            ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
                                            ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation 
                                            FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID

                                            left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
                                           {2}
                                            left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
                                            left join 
                                            (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
                                            left join 
                                            (select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
                                           {3}
) ee on ee.ID = emp.ID 
                                            where 1=1 
                                          
                                             and EmpStatus = '在职' 
                                             and  (ParentID in ({0}) or o.ID in ({0}))
                                             and (emp.Name like '%{1}%' or dbo.f_GetPy(emp.Name) like '{1}%')
                                            ) a  where  1=1
                                            ", OrgID, name,iszgxl == "true" ? " left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID"
    : "left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID", iszgxl == "true" ? @" left join (select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,edu.IsTopEducation from Emp_BasicInfo emp
left join
(select * from Emp_Education where IsTopEducation = 1) edu on emp.ID = edu.EmpBasicID" : @"left join
(select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,edu.IsTopEducation from Emp_BasicInfo emp 
left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
where 1=1 and " + highQueryCondition + "");
                        #endregion
                        #region

                        sql11 = string.Format(@" row_number()over(order by a.orgID ) Sequence from ( SELECT distinct  emp.[ID]
                                            ,emp.[Name] ,[CardNo],[Gender],kechuji
                                            ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
                                            ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
                                          --  ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end
,[BirthPlace] ,NativePlace,[Native] ,[Political],[Health],Password,SID,JCGZNX
                                            ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
                                            ,OrgID,o.Name OrgName ,PositionName ,[RegisterNo],[OrganizationType]
                                            ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
                                            ,HuoDeFangShi ,HouseholdRegister ,[Marriage] ,[HomeAddress] ,[Phone],[EmpIdentity]
                                            ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
                                            ,[Special] ,[TechnicalPositions] ,emp.HasPhoto , emp.PhotoName ,[PersonnelType]
                                            ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
                                            ,'false' as IsSelected
                                            ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
                                            ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
                                            else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
                                            case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
                                            ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
                                            ee.EducationName,ee.EducationType
                                            ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
                                            ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
                                            ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation 
                                            FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID

                                            left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
                                         {2}
                                            left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
                                            left join 
                                            (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
                                            left join 
                                            (select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
                                        
                                           {3}
) ee on ee.ID = emp.ID 
                                            where 1=1 
                                          
                                             and EmpStatus = '在职' 
                                             and  (ParentID in ({0}) or o.ID in ({0}))
                                             and (emp.Name like '%{1}%' or dbo.f_GetPy(emp.Name) like '{1}%')
                                            ) a  where  1=1
                                            ", OrgID, name,iszgxl == "true" ? " left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID"
    : "left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID", iszgxl == "true" ? @" left join (select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,edu.IsTopEducation from Emp_BasicInfo emp 
left join
(select * from Emp_Education where IsTopEducation = 1) edu on emp.ID = edu.EmpBasicID" : @"left join
(select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,edu.IsTopEducation from Emp_BasicInfo emp
left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
where 1=1 and " + highQueryCondition + "");
                        #endregion
                    }
                    else
                    {
                        #region
                        sql =
                            @" row_number()over(order by a.orgID ) Sequence,a.* from ( SELECT distinct  emp.[ID]
     ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
    --  ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end
      ,[BirthPlace]
      ,NativePlace,Password,SID,JCGZNX
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress],kechuji
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions]
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
        ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation  
FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
";
                        sql += iszgxl == "true" ? " left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID"
    : "left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID";

                        sql += @"
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
 left join
(select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,IsTopEducation from Emp_BasicInfo emp 
"; sql += iszgxl == "true" ? @"
left join
(select * from Emp_Education where IsTopEducation = 1) edu on emp.ID = edu.EmpBasicID
" : @"left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
where 1=1 and " + highQueryCondition + ""; sql += @"
) ee on ee.ID = emp.ID where EmpStatus = '" + empstatus + @"' and (emp.Name like '%" + name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + @"%')
and 1=1 {0}) a where  1=1";
                        #endregion
                        #region
                        sql11 =
                            @" row_number()over(order by a.orgID ) Sequence from ( SELECT distinct  emp.[ID]
     ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
    --  ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end
      ,[BirthPlace]
      ,NativePlace,Password,SID,JCGZNX
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress],kechuji
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions]
 ,emp.HasPhoto
      , emp.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
        ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation  
FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = emp.ID AND duty.JobStatus='在任'
";
                        sql11 += iszgxl == "true" ? " left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= emp.ID"
    : "left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID";

                        sql11 += @"
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = emp.ID
 left join
(select emp.ID,EducationName,EducationType,GraduationDate,edu.School,edu.Professional,IsTopEducation from Emp_BasicInfo emp
"; sql11 += iszgxl == "true" ? @"
left join
(select * from Emp_Education where IsTopEducation = 1) edu on emp.ID = edu.EmpBasicID
" : @"left join dbo.Emp_Education edu on  edu.EmpBasicID= emp.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(emp.ID)
where 1=1 and " + highQueryCondition + ""; sql11 += @"

) ee on ee.ID = emp.ID where EmpStatus = '" + empstatus + @"' and (emp.Name like '%" + name + "%'" + " or dbo.f_GetPy(emp.Name) like '" + name + @"%')
and 1=1 {0}) a where  1=1";
                        #endregion
                    }

                }

            //用于查看单个员工
            else
            {
                #region
                sql = string.Format(@"SELECT top " + (end - start) + @" row_number()over(order by a.ID ) Sequence, a.[ID]
      ,a.[Name]
      ,[CardNo]
      ,[Gender]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
      ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else [TuiXiuDate] end [TuiXiuDate]
     -- ,JuTuiXiuDate
,JuTuiXiuDate=case when datediff(YY,GETDATE(),TuiXiuDate) < 0 then 0 else datediff(YY,GETDATE(),TuiXiuDate) end
      ,[BirthPlace],kechuji
      ,NativePlace,Password,SID,JCGZNX
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,OrgID,o.Name OrgName
        ,PositionName=duty.DutyName
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,HuoDeFangShi
      ,HouseholdRegister
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity]
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special],a.EmpStatus
      ,[TechnicalPositions]
 ,a.HasPhoto
      , a.PhotoName
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,'false' as IsSelected
      ,ed.DutyNo,ed.DutyName,ed.DutyLevel,ed.DutyAttribute,case when ed.OKDate = '1900-01-01 00:00:00.000' then null else ed.OKDate end OKDate,ed.ServiceMode
      ,case when ed.JobDate = '1900-01-01 00:00:00.000' then null 
      else ed.JobDate end JobDate,duty.OrganizationName,duty.WorkCharge,duty.Sec Secstring,duty.IsExceptionalPromotion,duty.LianRen,
      case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
      ,case when ed.RXRZWSXSJ = '1900-01-01 00:00:00.000' then null else CONVERT(varchar(12) , ed.RXRZWSXSJ, 23 ) end RXRZWSXSJ,
      ee.EducationName,ee.EducationType
      ,case when ee.GraduationDate = '1900-01-01 00:00:00.000' then null else ee.GraduationDate end GraduationDate
      ,ee.School,ee.Professional,case when TuiXiuDate != '1900-01-01 00:00:00.000' then (CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) end  TuiXiuDateString
        ,Convert(varchar(11),degree.educationDate, 23) as DegreeGraduation  
FROM Emp_BasicInfo a left join OrgForHR o on a.orgid=o.ID
left JOIN dbo.Emp_Duty duty ON duty.EmpbasicID = a.ID AND duty.JobStatus='在任'
left join dbo.Emp_Education edu on edu.IsTopEducation=1 and edu.EmpBasicID= a.ID
left join dbo.emp_degree degree on degree.ID = dbo.fun_GetTopestDegree(a.ID)
  left join 
  (select emp1.ID,DutyNo,DutyName,DutyLevel,DutyAttribute,OKDate,ServiceMode,JobDate,RXRZWSXSJ,JobStatus from Emp_BasicInfo emp1 
left join 
(select * from Emp_Duty where JobStatus = '在任') ed1 on emp1.ID = ed1.EmpbasicID) ed on ed.ID = a.ID
  left join Emp_Education ee on ee.EmpBasicID = a.ID and ee.IsTopEducation = 1 where a.ID in {0} and EmpStatus = '" + empstatus + @"'", EmpID);
                sql += "  {0}";
                #endregion
            }
            if (!string.IsNullOrEmpty(highQueryCondition))
            {
                sql = string.Format(sql, " and  " + highQueryCondition);
                sql11 = string.Format(sql11, " and  " + highQueryCondition);
            }
            else
            {
                sql = string.Format(sql, " and 1=1");
                sql11 = string.Format(sql11, " and 1=1");
            }
            if (keji == true && chuji == false)
            {
                sql += " and a.kechuji = 0";
                sql11 += " and a.kechuji = 0";
            }
            if (chuji == true && keji == false)
            {
                sql += " and a.kechuji = 1";
                sql11 += " and a.kechuji = 1";
            }
            if (chuji == false && keji == false)
            {
                sql += " and a.kechuji = 0";
                sql11 += " and a.kechuji = 0";
            }

            #region  分页
            string sqlfy = "";
            if (EmpID == "0")
                sqlfy = string.Format(@"select top {2} * from 
( select {0}) aa where aa.Sequence not in
 (select top {3} {1}) order by OrgID", sql, sql11, (end - start), start);
            else
                sqlfy = sql;
            #endregion
            sql += " order by OrgID";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sqlfy);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<Emp_BasicInfoModel> GetEmp_BasicInfoByCon(string con)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql = string.Format(@"select OrgName=(select top 1 Name from OrgForHR where ID=OrgID)
,DutyName=(select top 1 DutyNo from Emp_Duty where EmpbasicID=eb.ID and JobStatus='在任')
,DutyNo=(select top 1 DutyName from Emp_Duty where EmpbasicID=eb.ID and JobStatus='在任')
,DutyLevel=(select top 1 DutyLevel from Emp_Duty where EmpbasicID=eb.ID and JobStatus='在任')
,DutyAttribute=(select top 1 DutyAttribute from Emp_Duty where EmpbasicID=eb.ID and JobStatus='在任')
,* from Emp_BasicInfo eb where 1=1 and  EmpStatus='在职' and  KeChuJi=0 {0}", con);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <returns></returns>
        public ObservableCollection<Emp_BasicInfoModel> GetDanWeiEmp_BasicInfos(string OrgID, string empstatus,bool keji,bool chuji)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql =string.Format(@"SELECT row_number()over(order by emp.ID ) Sequence, emp.[ID]
      ,emp.[Name]
      ,[CardNo]
      ,[Gender]
      ,[BirthPlace],SID,JCGZNX
      ,[HouseholdRegister]
         ,case when [TuiXiuDate] = '1900-01-01 00:00:00.000' then null else TuiXiuDate end TuiXiuDate
--,JuTuiXiuDate
,JuTuiXiuDate=datediff(YY,GETDATE(),TuiXiuDate)
,HuoDeFangShi
      ,[Native]
      ,[Political]
      ,[Health]
      ,case when [WorkDate] = '1900-01-01 00:00:00.000' then null else WorkDate end WorkDate
      ,[RegisterNo]
      ,[OrganizationType]
      ,case when [NowWorkDate] = '1900-01-01 00:00:00.000' then null else [NowWorkDate] end [NowWorkDate]
      ,[NativePlace]
      ,[Marriage]
      ,[HomeAddress]
      ,[Phone]
      ,[EmpIdentity],kechuji
      ,case when [PartyTime] = '1900-01-01 00:00:00.000' then null else [PartyTime] end [PartyTime]
      ,[Special]
      ,[TechnicalPositions]
      ,[PersonnelType]
      ,case when [RegisterDate] = '1900-01-01 00:00:00.000' then null else [RegisterDate] end [RegisterDate]
      ,case when [Birthday] = '1900-01-01 00:00:00.000' then null else [Birthday] end [Birthday]
        ,OrgID,o.Name OrgName,case when StatusDate = '1900-01-01 00:00:00.000' then null else StatusDate end StatusDate
        ,PositionName,'false' as IsSelected,(CONVERT(varchar, YEAR(TuiXiuDate)) + '-' + CONVERT(varchar, MONTH(TuiXiuDate))) as TuiXiuDateString 
  FROM Emp_BasicInfo emp left join OrgForHR o on emp.orgid=o.ID where EmpStatus in ({0}) and  1=1 ", empstatus);
            if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
            {
                sql += " and  (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) ";
            }
            if (keji == true && chuji == false)
            {
                sql += " and emp.kechuji = 0";
                sql += " and emp.kechuji = 0";
            }
            if (chuji == true && keji == false)
            {
                sql += " and emp.kechuji = 1";
                sql += " and emp.kechuji = 1";
            }
            if (chuji == false && keji == false)
            {
                sql += " and emp.kechuji = 0";
                sql += " and emp.kechuji = 0";
            }
            sql += " order by emp.[Name]";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;

        }
        public int AddEmp_BasicInfo(Emp_BasicInfoModel item)
        {
           
           
                string yanzhengsql = string.Format(@"select * from Emp_BasicInfo where name = '{0}' and cardno = '{1}'",
                                              item.Name, item.CardNo);
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(yanzhengsql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    return -1;
                }
                string sql = string.Format(@"INSERT INTO Emp_BasicInfo
           ([Name]
           ,[CardNo]
           ,[Gender]
           ,[BirthPlace]
           ,[HouseholdRegister]
           ,[Native]
           ,[Political]
           ,[Health]
           ,[WorkDate]
           ,[RegisterNo]
           ,[OrganizationType]
           ,[NowWorkDate]
           ,[NativePlace]
           ,[Marriage]
           ,[HomeAddress]
           ,[Phone]
           ,[EmpIdentity]
           ,[PartyTime]
           ,[Special]
           ,[TechnicalPositions]
           ,[PersonnelType]
           ,[RegisterDate]
            ,[Birthday]
,OrgID
        ,PositionName,TuiXiuDate,JuTuiXiuDate,HuoDeFangShi,EmpStatus,StatusDate,SID,JCGZNX)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}'
           ,'{20}'
           ,'{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}');SELECT @@IDENTITY", item.Name, item.CardNo, item.Gender, item.BirthPlace, item.HouseholdRegister, item.Native, item.Political, item.Health,
                        item.WorkDate, item.RegisterNo, item.OrganizationType, item.NowWorkDate, item.NativePlace, item.Marriage, item.HomeAddress,
                        item.Phone, item.EmpIdentity, item.PartyTime, item.Special, item.TechnicalPositions, item.PersonnelType, item.RegisterDate, item.Birthday,
                        item.OrgID, item.PositionName, item.TuiXiuDate, item.JuTuiXiuDate, item.HuoDeFangShi, item.EmpStatus, item.StatusDate,item.SID,item.JCGZNX);

                ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                if (item.OrgID != 46 && item.OrgID != 47)
                {
                    InsertCheGai(Convert.ToInt32(ds.Tables[0].Rows[0][0]), "新增人员", "开卡表");
                }

                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
         
        }



        public bool UpdateEmp_BasicInfo(Emp_BasicInfoModel item)
        {
            string sql = string.Format(@"UPDATE Emp_BasicInfo
   SET [Name] = '{0}'
      ,[CardNo] = '{1}'
      ,[Gender] = '{2}'
      ,[BirthPlace] = '{3}'
      ,[HouseholdRegister] = '{4}'
      ,[Native] = '{5}'
      ,[Political] = '{6}'
      ,[Health] = '{7}'
      ,[WorkDate] = '{8}'
      ,[RegisterNo] = '{9}'
      ,[OrganizationType] = '{10}'
      ,[NowWorkDate] = '{11}'
      ,[NativePlace] = '{12}'
      ,[Marriage] = '{13}'
      ,[HomeAddress] = '{14}'
      ,[Phone] = '{15}'
      ,[EmpIdentity] = '{16}'
      ,[PartyTime] = '{17}'
      ,[Special] = '{18}'
      ,[TechnicalPositions] = '{19}'
      ,[PersonnelType] = '{20}'
      ,[RegisterDate] = '{21}'
        ,[Birthday] = '{22}'
,OrgID= '{23}'
        ,PositionName= '{24}'
,[TuiXiuDate] = '{25}'
,JuTuiXiuDate= '{26}'
        ,HuoDeFangShi= '{27}',empstatus= '{28}',StatusDate = '{29}',SID='{30}',JCGZNX='{31}'
 WHERE ID = {32}", item.Name, item.CardNo, item.Gender, item.BirthPlace, item.HouseholdRegister, item.Native, item.Political, item.Health,
                    item.WorkDate, item.RegisterNo, item.OrganizationType, item.NowWorkDate, item.NativePlace, item.Marriage, item.HomeAddress,
                    item.Phone, item.EmpIdentity, item.PartyTime, item.Special, item.TechnicalPositions, item.PersonnelType, item.RegisterDate, item.Birthday
                    , item.OrgID, item.PositionName, item.TuiXiuDate, item.JuTuiXiuDate, item.HuoDeFangShi, item.EmpStatus, item.StatusDate,item.SID,item.JCGZNX, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_BasicInfo(string ID)
        {
            string sql = @"delete from Emp_Assessment where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_Degree where EmpbasicID in  " + ID + ";" +
                                       "delete from Emp_Duty where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_Education where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_FamilyRelation where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_Punishment where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_Resume where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_RewardInfo where EmpbasicID in " + ID + ";" +
                                       "delete from Emp_BasicInfo where ID in  " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 职务信息
        public ObservableCollection<Emp_Duty> GetEmp_Dutys(string EmpID, int ID)
        {
            ObservableCollection<Emp_Duty> rights = new ObservableCollection<Emp_Duty>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[DutyNo]
      ,[DutyName]
      ,[DutyLevel]
      ,[DutyAttribute]
      ,case when [OKDate] = '1900-01-01 00:00:00.000' then null else [OKDate] end [OKDate]
      ,[OrganizationName]
      ,[Sec]
,case when [BRZDQRQ] = '1900-01-01 00:00:00.000' then null else [BRZDQRQ] end [BRZDQRQ]
      ,[WorkCharge]
      ,[ServiceMode]
      ,[IsExceptionalPromotion]
      ,[JobStatus],RenQi,RZSYQ,LianRen,IsKaoHe
      ,case when [JobDate]= '1900-01-01 00:00:00.000' then null else [JobDate] end [JobDate]
,case when [GRStartDate]= '1900-01-01 00:00:00.000' then null else [GRStartDate] end [GRStartDate]
,case when [GREndDate]= '1900-01-01 00:00:00.000' then null else [GREndDate] end [GREndDate]
,case when [RXRZWSXSJ]= '1900-01-01 00:00:00.000' then null else [RXRZWSXSJ] end [RXRZWSXSJ]
,emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Duty
ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where EmpbasicID in {0} order by EmpbasicID, OKDate", EmpID);
            else
            {
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[DutyNo]
      ,[DutyName]
      ,[DutyLevel]
      ,[DutyAttribute],RenQi,RZSYQ,LianRen,IsKaoHe
,case when [BRZDQRQ] = '1900-01-01 00:00:00.000' then null else [BRZDQRQ] end [BRZDQRQ]
      ,case when [OKDate] = '1900-01-01 00:00:00.000' then null else [OKDate] end [OKDate]
      ,[OrganizationName]
      ,[Sec]
      ,[WorkCharge]
      ,[ServiceMode]
      ,[IsExceptionalPromotion]
      ,[JobStatus],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
      ,case when [JobDate]= '1900-01-01 00:00:00.000' then null else [JobDate] end [JobDate]
,case when [GRStartDate]= '1900-01-01 00:00:00.000' then null else [GRStartDate] end [GRStartDate]
,case when [GREndDate]= '1900-01-01 00:00:00.000' then null else [GREndDate] end [GREndDate]
,case when [RXRZWSXSJ]= '1900-01-01 00:00:00.000' then null else [RXRZWSXSJ] end [RXRZWSXSJ]
  FROM Emp_Duty ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0} order by OKDate", ID);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_Duty.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<Emp_Duty> GetEmp_DutysJobStatus(string EmpID, int ID)
        {
            ObservableCollection<Emp_Duty> rights = new ObservableCollection<Emp_Duty>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select ed.[ID]
      ,[EmpbasicID]
      ,[DutyNo]
      ,[DutyName]
      ,[DutyLevel]
      ,[DutyAttribute]
      ,case when [OKDate] = '1900-01-01 00:00:00.000' then null else [OKDate] end [OKDate]
      ,[OrganizationName]
      ,[Sec]
,case when [BRZDQRQ] = '1900-01-01 00:00:00.000' then null else [BRZDQRQ] end [BRZDQRQ]
      ,[WorkCharge]
      ,[ServiceMode]
      ,[IsExceptionalPromotion]
      ,[JobStatus],RenQi,RZSYQ,LianRen,IsKaoHe
      ,case when [JobDate]= '1900-01-01 00:00:00.000' then null else [JobDate] end [JobDate]
,case when [GRStartDate]= '1900-01-01 00:00:00.000' then null else [GRStartDate] end [GRStartDate]
,case when [GREndDate]= '1900-01-01 00:00:00.000' then null else [GREndDate] end [GREndDate]
,case when [RXRZWSXSJ]= '1900-01-01 00:00:00.000' then null else [RXRZWSXSJ] end [RXRZWSXSJ]
,cc.* from (
select eb.ID eid,eb.Name EmpName,eb.CardNo EmpNo,eb.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=eb.orgid)
, jl=case when (select  COUNT(id) from Emp_Duty where EmpbasicID=eb.ID)>0 
then ''
else '无记录' end
from Emp_BasicInfo eb 

where eb.ID in {0}
) cc left join Emp_Duty ed on ed.EmpbasicID=cc.eid and JobStatus in('在任','兼任')  order by eid,jl desc", EmpID);
            else
            {
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[DutyNo]
      ,[DutyName]
      ,[DutyLevel]
      ,[DutyAttribute],RenQi,RZSYQ,LianRen,IsKaoHe
,case when [BRZDQRQ] = '1900-01-01 00:00:00.000' then null else [BRZDQRQ] end [BRZDQRQ]
      ,case when [OKDate] = '1900-01-01 00:00:00.000' then null else [OKDate] end [OKDate]
      ,[OrganizationName]
      ,[Sec]
      ,[WorkCharge]
      ,[ServiceMode]
      ,[IsExceptionalPromotion]
      ,[JobStatus],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
      ,case when [JobDate]= '1900-01-01 00:00:00.000' then null else [JobDate] end [JobDate]
,case when [GRStartDate]= '1900-01-01 00:00:00.000' then null else [GRStartDate] end [GRStartDate]
,case when [GREndDate]= '1900-01-01 00:00:00.000' then null else [GREndDate] end [GREndDate]
,case when [RXRZWSXSJ]= '1900-01-01 00:00:00.000' then null else [RXRZWSXSJ] end [RXRZWSXSJ]
  FROM Emp_Duty ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0} and JobStatus in('在任','兼任') order by OKDate", ID);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_Duty.FillEntity(dr));
            }
            return rights;
        }

        public bool AddEmp_Duty(Emp_Duty item)
        {
            string sql1 = "";
            if (item.JobStatus == "在任")
            {
                sql1 = ";update Emp_Duty set JobStatus = '不在任' where EmpbasicID = " + item.EmpBasicID + " and JobStatus = '在任' ";
            }
            if (!string.IsNullOrEmpty(sql1))
                AccessHelper.GetInstance().ExecuteNonQuery(sql1);
            string sql = string.Format(@"INSERT INTO Emp_Duty
           ([EmpbasicID]
           ,[DutyNo]
           ,[DutyName]
           ,[DutyLevel]
           ,[DutyAttribute]
           ,[OKDate]
           ,[OrganizationName]
           ,[Sec]
           ,[WorkCharge]
           ,[ServiceMode]
           ,[IsExceptionalPromotion]
           ,[JobStatus]
           ,[JobDate],RenQi,RZSYQ,LianRen,BRZDQRQ,RXRZWSXSJ,GRStartDate,GREndDate)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}','{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}','{17}','{18}','{19}')", item.EmpBasicID, item.DutyNo, item.DutyName, item.DutyLevel, item.DutyAttribute, item.OKDate, item.OrganizationName, item.Sec,
                    item.WorkCharge, item.ServiceMode, item.IsExceptionalPromotion, item.JobStatus, item.JobDate, item.RenQi, item.RZSYQ, item.LianRen, item.BRZDQRQ, item.RXRZWSXSJ, item.GRStartDate, item.GREndDate);
            if (item.DutyLevel == "厅局级正职（巡视员）" || item.DutyLevel == "厅局级副职（副巡视员）" || item.DutyLevel == "县处级副职（副调研员）" || item.DutyLevel == "县处级正职（调研员）")
            {
                sql += " ;update Emp_BasicInfo set kechuji = 1 where id =" + item.EmpBasicID;
            }
            
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_Duty(Emp_Duty item)
        {
            string sql = string.Format(@"UPDATE Emp_Duty
   SET [EmpbasicID] = '{0}'
      ,[DutyNo] = '{1}'
      ,[DutyName] = '{2}'
      ,[DutyLevel] = '{3}'
      ,[DutyAttribute] = '{4}'
      ,[OKDate] = '{5}'
      ,[OrganizationName] = '{6}'
      ,[Sec] = '{7}'
      ,[WorkCharge] = '{8}'
      ,[ServiceMode] = '{9}'
      ,[IsExceptionalPromotion] = '{10}'
      ,[JobStatus] = '{11}'
      ,[JobDate] = '{12}',RenQi= '{13}',RZSYQ= '{14}',LianRen= '{15}',BRZDQRQ= '{16}',RXRZWSXSJ = '{17}',GRStartDate= '{18}',GREndDate = '{19}'
 WHERE ID = {20}", item.EmpBasicID, item.DutyNo, item.DutyName, item.DutyLevel, item.DutyAttribute, item.OKDate, item.OrganizationName, item.Sec,
                    item.WorkCharge, item.ServiceMode, item.IsExceptionalPromotion, item.JobStatus, item.JobDate, item.RenQi, item.RZSYQ, item.LianRen
                    , item.BRZDQRQ, item.RXRZWSXSJ, item.GRStartDate, item.GREndDate, item.ID);
            if (item.JobStatus == "不在任")
                sql += " ;delete from BianZhiJieYongEmp where EmpBasicID =" + item.EmpBasicID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_Duty(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Duty where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 简历信息
        public ObservableCollection<Emp_ResumeModel> GetEmp_Resume(string EmpID, int ID)
        {
            ObservableCollection<Emp_ResumeModel> rights = new ObservableCollection<Emp_ResumeModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[BeginDate]
      ,ed.[EndDate]
      ,ed.[Unit]
      ,ed.[Duty]
      ,ed.[Witness]
      ,ed.[IsPeriod]
      ,case when [IsPeriod] = 0 then '否' else '是' end IsPeriodString,emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Resume ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by EmpbasicID, BeginDate", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[BeginDate]
      ,[EndDate]
      ,[Unit]
      ,[Duty]
      ,[Witness]
      ,[IsPeriod],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
,case when [IsPeriod] = 0 then '否' else '是' end IsPeriodString
  FROM Emp_Resume ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0} order by BeginDate", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_ResumeModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<Emp_ResumeModel> GetEmp_ResumeBYJL(string EmpID, int ID)
        {
            ObservableCollection<Emp_ResumeModel> rights = new ObservableCollection<Emp_ResumeModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[BeginDate]
      ,ed.[EndDate]
      ,ed.[Unit]
      ,ed.[Duty]
      ,ed.[Witness]
      ,ed.[IsPeriod]
      ,case when [IsPeriod] = 0 then '否' else '是' end IsPeriodString
,cc.* from (
select eb.ID eid,eb.Name EmpName,eb.CardNo EmpNo,eb.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=eb.orgid)
, jl=case when (select  COUNT(id) from Emp_Resume where EmpbasicID=eb.ID)>0 
then ''
else '无记录' end
from Emp_BasicInfo eb 

where eb.ID in {0}
) cc left join Emp_Resume ed on ed.EmpbasicID=cc.eid order by eid,jl desc", EmpID);
          
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_ResumeModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_Resume(Emp_ResumeModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Resume
           ([EmpbasicID]
           ,[BeginDate]
           ,[EndDate]
           ,[Unit]
           ,[Duty]
           ,[Witness]
           ,[IsPeriod])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.BeginDate, item.EndDate, item.Unit, item.Duty, item.Witness, item.IsPeriod);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_ResumeModel(Emp_ResumeModel item)
        {
            string sql = string.Format(@"update Emp_Resume
   SET [EmpbasicID] = {0}
      ,[BeginDate] = '{1}'
      ,[EndDate] = '{2}'
      ,[Unit] = '{3}'
      ,[Duty] = '{4}'
      ,[Witness] = '{5}'
      ,[IsPeriod] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.BeginDate, item.EndDate, item.Unit, item.Duty, item.Witness, item.IsPeriod, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_ResumeModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Resume where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 学历信息
        public ObservableCollection<Emp_EducationModel> GetEmp_EducationModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_EducationModel> rights = new ObservableCollection<Emp_EducationModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpBasicID]
      ,ed.[EducationName]
      ,ed.[EducationType]
      ,ed.[EducationaSystem]
      ,ed.[GraduationDate]
      ,ed.[School]
      ,ed.[Professional]
      ,ed.[IsTopEducation]
,case when [IsTopEducation] = 0 then '否' else '是' end IsTopEducationString,emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Education ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpBasicID]
      ,ed.[EducationName]
      ,ed.[EducationType]
      ,ed.[EducationaSystem]
      ,ed.[GraduationDate]
      ,ed.[School]
      ,ed.[Professional]
      ,ed.[IsTopEducation],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
,case when ed.[IsTopEducation] = 0 then '否' else '是' end IsTopEducationString
  FROM Emp_Education ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_EducationModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<Emp_EducationModel> GetEmp_EducationModelByJL(string EmpID, int ID)
        {
            ObservableCollection<Emp_EducationModel> rights = new ObservableCollection<Emp_EducationModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select ed.[ID]
      ,ed.[EmpBasicID]
      ,ed.[EducationName]
      ,ed.[EducationType]
      ,ed.[EducationaSystem]
      ,ed.[GraduationDate]
      ,ed.[School]
      ,ed.[Professional]
      ,ed.[IsTopEducation]
,case when [IsTopEducation] = 0 then '否' else '是' end IsTopEducationString
,cc.* from (
select eb.ID eid,eb.Name EmpName,eb.CardNo EmpNo,eb.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=eb.orgid)
, jl=case when (select  COUNT(id) from Emp_Education where EmpbasicID=eb.ID)>0 
then ''
else '无记录' end
from Emp_BasicInfo eb 

where eb.ID in {0}
) cc left join Emp_Education ed on ed.EmpbasicID=cc.eid 
order by eid,jl desc", EmpID);
         
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_EducationModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_EducationModel(Emp_EducationModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Education
           ([EmpBasicID]
           ,[EducationName]
           ,[EducationType]
           ,[EducationaSystem]
           ,[GraduationDate]
           ,[School]
           ,[Professional]
           ,[IsTopEducation])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}')", item.EmpBasicID, item.EducationName, item.EducationType, item.EducationaSystem, item.GraduationDate, item.School, item.Professional, item.IsTopEducation);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_EducationModel(Emp_EducationModel item)
        {
            string sql = string.Format(@"UPDATE Emp_Education
   SET [EmpBasicID] = {0}
      ,[EducationName] = '{1}'
      ,[EducationType] = '{2}'
      ,[EducationaSystem] = '{3}'
      ,[GraduationDate] = '{4}'
      ,[School] = '{5}'
      ,[Professional] = '{6}'
      ,[IsTopEducation] = '{7}'
 WHERE ID = {8}", item.EmpBasicID, item.EducationName, item.EducationType, item.EducationaSystem, item.GraduationDate, item.School, item.Professional, item.IsTopEducation, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_EducationModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Education where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 学位信息
        public ObservableCollection<Emp_DegreeModel> GetEmp_DegreeModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_DegreeModel> rights = new ObservableCollection<Emp_DegreeModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[DegreeName]
      ,ed.[LearningType]
      ,ed.[EducationalSystem]
      ,ed.[EducationDate]
      ,ed.[School],SYXWSJ
      ,ed.[Professional],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Degree ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[DegreeName]
      ,[LearningType]
      ,[EducationalSystem]
      ,[EducationDate]
      ,[School],SYXWSJ
      ,[Professional],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Degree ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_DegreeModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<Emp_DegreeModel> GetEmp_DegreeModelBYJL(string EmpID, int ID)
        {
            ObservableCollection<Emp_DegreeModel> rights = new ObservableCollection<Emp_DegreeModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[DegreeName]
      ,ed.[LearningType]
      ,ed.[EducationalSystem]
      ,ed.[EducationDate]
      ,ed.[School]
      ,ed.[Professional],SYXWSJ
,cc.* from (
select eb.ID eid,eb.Name EmpName,eb.CardNo EmpNo,eb.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=eb.orgid)
, jl=case when (select  COUNT(id) from Emp_Degree where EmpbasicID=eb.ID)>0 
then ''
else '无记录' end
from Emp_BasicInfo eb 

where eb.ID in {0}
) cc left join Emp_Degree ed on ed.EmpbasicID=cc.eid 
order by eid,jl desc", EmpID);
          
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_DegreeModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_DegreeModel(Emp_DegreeModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Degree
           ([EmpbasicID]
           ,[DegreeName]
           ,[LearningType]
           ,[EducationalSystem]
           ,[EducationDate]
           ,[School]
           ,[Professional],SYXWSJ)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}','{7}')", item.EmpBasicID, item.DegreeName, item.LearningType, item.EducationalSystem, item.EducationDate, item.School, item.Professional,item.SYXWSJ);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_DegreeModel(Emp_DegreeModel item)
        {
            string sql = string.Format(@"UPDATE Emp_Degree
   SET [EmpbasicID] = {0}
      ,[DegreeName] = '{1}'
      ,[LearningType] = '{2}'
      ,[EducationalSystem] = '{3}'
      ,[EducationDate] = '{4}'
      ,[School] = '{5}'
      ,[Professional] = '{6}',SYXWSJ='{7}'
 WHERE ID = {8}", item.EmpBasicID, item.DegreeName, item.LearningType, item.EducationalSystem, item.EducationDate, item.School, item.Professional,item.SYXWSJ, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_DegreeModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Degree where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 考核信息
        public ObservableCollection<Emp_AssessmentModel> GetEmp_AssessmentModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_AssessmentModel> rights = new ObservableCollection<Emp_AssessmentModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.ID
      ,ed.[EmpbasicID]
      ,ed.[AssessmentType]
      ,ed.[AssessmentResult]
      ,ed.[AssessmentDate],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Assessment ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpbasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[AssessmentType]
      ,ed.[AssessmentResult]
      ,ed.[AssessmentDate],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Assessment ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0} ", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_AssessmentModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<Emp_AssessmentModel> GetEmp_AssessmentModelBySND(string EmpID, int ID)
        {
            ObservableCollection<Emp_AssessmentModel> rights = new ObservableCollection<Emp_AssessmentModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select * from (
select distinct ID=0,AssessmentType='',AssessmentDate='',eb.ID EmpBasicID,eb.Name EmpName,eb.CardNo EmpNo,eb.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=eb.orgid)
, AssessmentResult=case when (select  COUNT(id) from Emp_Assessment where EmpbasicID=eb.ID)>0 
then case when (select top 1 AssessmentResult from Emp_Assessment where EmpbasicID=eb.ID and year(AssessmentDate)=YEAR(GETDATE())-1 and AssessmentType='年度考核')IS NULL then '是' else '否' end
else '无记录' end
from Emp_BasicInfo eb 
where eb.ID in {0} 
 )cc
order by AssessmentResult", EmpID);
          
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_AssessmentModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_AssessmentModel(Emp_AssessmentModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Assessment
           ([EmpbasicID]
           ,[AssessmentType]
           ,[AssessmentResult]
           ,[AssessmentDate])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}')", item.EmpBasicID, item.AssessmentType, item.AssessmentResult, item.AssessmentDate);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_AssessmentModel(Emp_AssessmentModel item)
        {
            string sql = string.Format(@"UPDATE Emp_Assessment
   SET [EmpbasicID] = {0}
      ,[AssessmentType] = '{1}'
      ,[AssessmentResult] = '{2}'
      ,[AssessmentDate] = '{3}'
 WHERE ID = {4}", item.EmpBasicID, item.AssessmentType, item.AssessmentResult, item.AssessmentDate, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_AssessmentModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Assessment where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 奖惩信息
        public ObservableCollection<Emp_RewardInfoModel> GetEmp_RewardInfoModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_RewardInfoModel> rights = new ObservableCollection<Emp_RewardInfoModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[RewardNo]
      ,ed.[RewardName]
      ,ed.[RewardReason]
      ,ed.[OtherRewardReason]
      ,ed.[OKDate]
      ,ed.[OKUnit],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_RewardInfo ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[RewardNo]
      ,ed.[RewardName]
      ,ed.[RewardReason]
      ,ed.[OtherRewardReason]
      ,ed.[OKDate]
      ,ed.[OKUnit],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_RewardInfo ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_RewardInfoModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<Emp_RewardInfoModel> GetEmp_RewardInfoModelByJL(string EmpID, int ID)
        {
            ObservableCollection<Emp_RewardInfoModel> rights = new ObservableCollection<Emp_RewardInfoModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[RewardNo]
      ,ed.[RewardName]
      ,ed.[RewardReason]
      ,ed.[OtherRewardReason]
      ,ed.[OKDate]
      ,ed.[OKUnit],cc.* from (
select eb.ID eid,eb.Name EmpName,eb.CardNo EmpNo,eb.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=eb.orgid)
, jl=case when (select  COUNT(id) from Emp_RewardInfo where EmpbasicID=eb.ID)>0 
then ''
else '无记录' end
from Emp_BasicInfo eb 

where eb.ID in {0}
) cc left join Emp_RewardInfo ed on ed.EmpbasicID=cc.eid  order by eid,jl desc", EmpID);
          
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_RewardInfoModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_RewardInfoModel(Emp_RewardInfoModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_RewardInfo
           ([EmpbasicID]
           ,[RewardNo]
           ,[RewardName]
           ,[RewardReason]
           ,[OtherRewardReason]
           ,[OKDate]
           ,[OKUnit])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.RewardNo, item.RewardName, item.RewardReason, item.OtherRewardReason, item.OKDate, item.OKUnit);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_RewardInfoModel(Emp_RewardInfoModel item)
        {
            string sql = string.Format(@"UPDATE Emp_RewardInfo
   SET [EmpbasicID] = {0}
      ,[RewardNo] = '{1}'
      ,[RewardName] = '{2}'
      ,[RewardReason] ='{3}'
      ,[OtherRewardReason] = '{4}'
      ,[OKDate] = '{5}'
      ,[OKUnit] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.RewardNo, item.RewardName, item.RewardReason, item.OtherRewardReason, item.OKDate, item.OKUnit, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_RewardInfoModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_RewardInfo where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 处分信息
        public ObservableCollection<Emp_PunishmentModel> GetEmp_PunishmentModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_PunishmentModel> rights = new ObservableCollection<Emp_PunishmentModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[PunishmentNo]
      ,ed.[PunishmentName]
      ,ed.[PunishmentReason]
      ,ed.[OkUnit]
      ,ed.[OKDate]
      ,ed.[PunishmentTimeLimit],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Punishment ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,[EmpbasicID]
      ,[PunishmentNo]
      ,[PunishmentName]
      ,[PunishmentReason]
      ,[OkUnit]
      ,[OKDate]
      ,[PunishmentTimeLimit],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_Punishment ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_PunishmentModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<Emp_PunishmentModel> GetEmp_PunishmentModelByJL(string EmpID, int ID)
        {
            ObservableCollection<Emp_PunishmentModel> rights = new ObservableCollection<Emp_PunishmentModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[PunishmentNo]
      ,ed.[PunishmentName]
      ,ed.[PunishmentReason]
      ,ed.[OkUnit]
      ,ed.[OKDate]
      ,ed.[PunishmentTimeLimit],cc.* from (
select eb.ID eid,eb.Name EmpName,eb.CardNo EmpNo,eb.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=eb.orgid)
, jl=case when (select  COUNT(id) from Emp_Punishment where EmpbasicID=eb.ID)>0 
then ''
else '无记录' end
from Emp_BasicInfo eb 

where eb.ID in {0}
) cc left join Emp_Punishment ed on ed.EmpbasicID=cc.eid  order by eid,jl desc
", EmpID);
          
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_PunishmentModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_PunishmentModel(Emp_PunishmentModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_Punishment
           ([EmpbasicID]
           ,[PunishmentNo]
           ,[PunishmentName]
           ,[PunishmentReason]
           ,[OkUnit]
           ,[OKDate]
           ,[PunishmentTimeLimit])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.PunishmentNo, item.PunishmentName, item.PunishmentReason, item.OkUnit, item.OKDate, item.PunishmentTimeLimit);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_PunishmentModel(Emp_PunishmentModel item)
        {
            string sql = string.Format(@"UPDATE Emp_Punishment
   SET [EmpbasicID] = {0}
      ,[PunishmentNo] = '{1}'
      ,[PunishmentName] = '{2}'
      ,[PunishmentReason] = '{3}'
      ,[OkUnit] = '{4}'
      ,[OKDate] = '{5}'
      ,[PunishmentTimeLimit] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.PunishmentNo, item.PunishmentName, item.PunishmentReason, item.OkUnit, item.OKDate, item.PunishmentTimeLimit, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_PunishmentModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_Punishment where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 家庭信息
        public ObservableCollection<Emp_FamilyRelationModel> GetEmp_FamilyRelationModel(string EmpID, int ID)
        {
            ObservableCollection<Emp_FamilyRelationModel> rights = new ObservableCollection<Emp_FamilyRelationModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[Name]
      ,ed.[Appellation]
      ,ed.[Unit]
      ,ed.[Duty]
      ,ed.[Political]
      ,ed.[Birthday],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_FamilyRelation ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID  where EmpbasicID in {0} order by ed.[EmpBasicID]", EmpID);
            else
                sql = string.Format(@"SELECT ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[Name]
      ,ed.[Appellation]
      ,ed.[Unit]
      ,ed.[Duty]
      ,ed.[Political]
      ,ed.[Birthday],emp.Name EmpName,emp.CardNo EmpNo,emp.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=emp.orgid)
  FROM Emp_FamilyRelation ed inner join Emp_BasicInfo emp on ed.EmpbasicID = emp.ID where ed.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_FamilyRelationModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<Emp_FamilyRelationModel> GetEmp_FamilyRelationModelByJL(string EmpID, int ID)
        {
            ObservableCollection<Emp_FamilyRelationModel> rights = new ObservableCollection<Emp_FamilyRelationModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select ed.[ID]
      ,ed.[EmpbasicID]
      ,ed.[Name]
      ,ed.[Appellation]
      ,ed.[Unit]
      ,ed.[Duty]
      ,ed.[Political]
      ,ed.[Birthday],cc.* from (
select eb.ID eid,eb.Name EmpName,eb.CardNo EmpNo,eb.SID,JCGZNX=(select top 1 Name from OrgForHR where ID=eb.orgid)
, jl=case when (select  COUNT(id) from Emp_FamilyRelation where EmpbasicID=eb.ID)>0 
then ''
else '无记录' end
from Emp_BasicInfo eb 

where eb.ID in {0}
) cc left join Emp_FamilyRelation ed on ed.EmpbasicID=cc.eid  order by eid,jl desc
", EmpID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_FamilyRelationModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddEmp_FamilyRelationModel(Emp_FamilyRelationModel item)
        {
            string sql = string.Format(@"INSERT INTO Emp_FamilyRelation
           ([EmpbasicID]
           ,[Name]
           ,[Appellation]
           ,[Unit]
           ,[Duty]
           ,[Political]
           ,[Birthday])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", item.EmpBasicID, item.Name, item.Appellation, item.Unit, item.Duty, item.Political, item.Birthday);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateEmp_FamilyRelationModel(Emp_FamilyRelationModel item)
        {
            string sql = string.Format(@"UPDATE Emp_FamilyRelation
   SET [EmpbasicID] = {0}
      ,[Name] = '{1}'
      ,[Appellation] = '{2}'
      ,[Unit] ='{3}'
      ,[Duty] = '{4}'
      ,[Political] = '{5}'
      ,[Birthday] = '{6}'
 WHERE ID = {7}", item.EmpBasicID, item.Name, item.Appellation, item.Unit, item.Duty, item.Political, item.Birthday, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeteleEmp_FamilyRelationModel(int ID)
        {
            string sql = string.Format(@"
delete from Emp_FamilyRelation where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 操作日志
        public int GetSystemMarkCounts(string dengluren, string caozuoren, string caozuoriqi)
        {
            string sql = "";
            //用于查看员工列表
            //            if (GetDengLuJueSe(dengluren) != "管理员" )
            //                sql = string.Format(@"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
            //  FROM system_mark emp  where Operator = '{0}' and 1=1", dengluren);
            //            else
            sql = string.Format(@"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
  FROM system_mark emp where 1=1 ");
            if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),UpdateDate,111) = convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }
        public ObservableCollection<System_MarkManageModel> GetSystemMark(string dengluren, int start, int end, string caozuoren, string caozuoriqi)
        {
            ObservableCollection<System_MarkManageModel> rights = new ObservableCollection<System_MarkManageModel>();
            string sql = "";
            //            if(dengluren != "admin")
            //                sql = string.Format(@"SELECT top " + (end - start) + @" s.[ID]
            //      ,[UpdateDate]
            //      ,[InvolvingSubset]
            //      ,[Content]
            //      ,[InvolvingPersonID]
            //      ,[Operator],[OPerationStyle]
            //      ,[InvolvingColumn],OldContent,emp.Name EmpName
            //  FROM System_Mark s left join Emp_BasicInfo emp on s.InvolvingPersonID = emp.ID where Operator = '{0}' and '{1}' and '{2}' and
            // s.ID not   in   
            //    (select   top " + start + @" s.ID from   System_Mark s inner join Emp_BasicInfo emp on s.InvolvingPersonID = emp.ID where Operator = '{0}' and '{1}' and '{2}' order by UpdateDate desc)", dengluren);
            //            else
            sql =
                string.Format(@"SELECT top " + (end - start) +
                              @" s.[ID]
      ,[UpdateDate]
      ,[InvolvingSubset]
      ,[Content]
      ,[InvolvingPersonID]
      ,[Operator],[OPerationStyle]
      ,[InvolvingColumn],OldContent,emp.Name EmpName
  FROM System_Mark s left join Emp_BasicInfo emp on s.InvolvingPersonID = emp.ID where 1=1 ");
            if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),UpdateDate,111) = convert(VARCHAR(30),Convert(datetime, '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            sql += @" and s.ID not   in   
    (select   top " + start +
                   @" s.ID from   System_Mark s inner join Emp_BasicInfo emp on s.InvolvingPersonID = emp.ID where 1=1 ";
            if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),UpdateDate,111) = convert(VARCHAR(30),Convert(datetime, '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            sql += @" order by UpdateDate desc) ";
            sql = sql + " order by UpdateDate desc ";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(System_MarkManageModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddSystem_Mark(string ZiJi, string OldContent, string NewContent, int EmpID, string CaoZuoFangShi, string ZiDuan, string DengLuRen)
        {
            string sql = string.Format(@"INSERT INTO System_Mark
           ([UpdateDate]
           ,[InvolvingSubset]
           ,[Content]
           ,[InvolvingPersonID]
           ,[Operator],[OPerationStyle]
      ,[InvolvingColumn],OldContent)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
,'{5}'
           ,'{6}','{7}')", DateTime.Now, ZiJi, NewContent, EmpID, DengLuRen, CaoZuoFangShi, ZiDuan, OldContent);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateSystem_Mark(System_MarkManageModel item)
        {
            string sql = string.Format(@"UPDATE System_Mark
   SET [UpdateDate] = '{0}'
      ,[InvolvingSubset] =  '{1}'
      ,[Content] =  '{2}'
      ,[InvolvingPersonID] =  '{3}'
      ,[Operator] =  '{4}',[OPerationStyle] = '{5}'
      ,[InvolvingColumn] = '{6}',OldContent = '{7}'
 WHERE ID = {8}", item.UpdateDate, item.InvolvingSubset, item.Content, item.InvolvingPersonID, item.Operator, item.OPerationStyle, item.InvolvingColumn, item.OldContent, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 车改登记
        public int GetCheGaiCounts(string caozuoren, string caozuoriqi, string endcaozuoriqi, string orgid, string status, int keyid, string dengjibiandong)
        {
            string sql = "";
            //用于查看员工列表
            //            if (GetDengLuJueSe(dengluren) != "管理员")
            //                sql = string.Format(@"SELECT case when count(emp.[ID])%20 != 0 then  count(emp.[ID])/20 + 1 else count(emp.[ID])/20 end Count
            //              FROM CheGai emp  where Operator = '{0}' and 1=1", dengluren);
            //            else
            sql = string.Format(@"SELECT case when count(cg.[ID])%20 != 0 then  count(cg.[ID])/20 + 1 else count(cg.[ID])/20 end Count
  FROM CheGai cg inner join dbo.Emp_BasicInfo emp on cg.EmpId = emp.id
  inner join orgforhr o on o.id = emp.orgid where cg.type = '{0}' and 1=1 ", dengjibiandong);
            //if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (orgid != "0") sql += " and o.id in (" + orgid + ")";
            if (keyid != 0) sql += " and cg.keyid = " + keyid;
            if (!string.IsNullOrEmpty(status) && status != "全部") sql += " and cg.[Status] = '" + status + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) >= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            if (!string.IsNullOrEmpty(endcaozuoriqi) && Convert.ToDateTime(endcaozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) <= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }
        public void InsertCheGai(int EmpID, string Type, string dengjibiandongtype)
        {
            string sql =
                string.Format(
                    @"INSERT INTO CheGai
           ([EmpID]
           ,[OldZhiJi]
           ,[Status]
           ,[Demo]
           ,[RelationID],keyid,type,CreateDate)
     select {0},ed.dutylevel,'未导出','{1}',{0},
case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='{2}' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='{2}' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,'{2}',getdate()
 from  dbo.Emp_BasicInfo emp
inner join orgforhr o on emp.orgid = o.id
     left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任'
      where emp.ID = {0}",
                    EmpID, Type, dengjibiandongtype);
            AccessHelper.GetInstance().ExcuteDataSet(sql); ;
        }
        public ObservableCollection<CheGaiModel> GetCheGai(string caozuoren, string caozuoriqi, string endcaozuoriqi, string orgid, string status, int start, int end, int keyid, string dengjibiandong)
        {
            ObservableCollection<CheGaiModel> rights = new ObservableCollection<CheGaiModel>();
            string sql =
                string.Format(@"SELECT top " + (end - start) +
                              @" cg.[ID],'false' as IsSelected
      ,[EmpID]
      ,[OldZhiJi]
      ,[OldJinE]
,ed.dutylevel as [NewZhiJi]
      ,case when [ChangeDate] = '1900-01-01 00:00:00.000' then null else ChangeDate end ChangeDate
      ,[NewZhiJi]
      ,[NewJinE]
      ,[Operator]
      ,[CreateDate]
      ,cg.[Status]
      ,cg.[Demo]
      ,[RelationID],emp.name EmpName,cg.type,cg.keyid,o.type OrgType,emp.Gender,ed.DutyNo NowDuty
  FROM CheGai cg inner join dbo.Emp_BasicInfo emp on cg.empid = emp.id inner join orgforhr o on o.id = emp.orgid
 left join dbo.Emp_Duty ed on emp.id = ed.empbasicid and jobstatus = '在任' where cg.type = '{0}' and 1=1 ", dengjibiandong);
            //if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (orgid != "0") sql += " and o.id in (" + orgid + ")";
            if (keyid != 0) sql += " and cg.keyid = " + keyid;
            if (!string.IsNullOrEmpty(status) && status != "全部") sql += " and cg.[Status] = '" + status + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) >= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            if (!string.IsNullOrEmpty(endcaozuoriqi) && Convert.ToDateTime(endcaozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) <= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            sql += @" and cg.ID not   in   
    (select   top " + start +
                   @" s.ID from   CheGai s inner join Emp_BasicInfo emp on s.EmpID = emp.ID inner join orgforhr o on o.id = emp.orgid  where 1=1 ";
            //if (!string.IsNullOrEmpty(caozuoren)) sql += " and Operator = '" + caozuoren + "'";
            if (orgid != "0") sql += " and o.id in (" + orgid + ")";
            if (keyid != 0) sql += " and s.keyid = " + keyid;
            if (!string.IsNullOrEmpty(status) && status != "全部") sql += " and s.[Status] = '" + status + "'";
            sql += " and cg.type = '" + dengjibiandong + "'";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) >= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            if (!string.IsNullOrEmpty(endcaozuoriqi) && Convert.ToDateTime(endcaozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) <= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            sql += @" order by CreateDate desc) ";
            sql = sql + " order by CreateDate desc ";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(CheGaiModel.FillEntity(dr));
            }
            return rights;
        }

        public bool DeteleCheGai(int ID)
        {
            string sql = string.Format(@"
delete from CheGai where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public CheGaiModel GetOneCheGai(int id)
        {
            ObservableCollection<CheGaiModel> rights = new ObservableCollection<CheGaiModel>();
            string sql =
                string.Format(@"SELECT cg.[ID],'false' as IsSelected
      ,[EmpID]
      ,[OldZhiJi]
      ,case when oldjine is null then zj.jine else oldjine end as [OldJinE]
      ,case when [ChangeDate] = '1900-01-01 00:00:00.000' then null else ChangeDate end ChangeDate
      ,ed.dutylevel as [NewZhiJi]
      ,case when newjine is null then zj1.jine else newjine end as [NewJinE]
      ,[Operator]
      ,[CreateDate]
      ,cg.[Status]
      ,cg.[Demo]
      ,[RelationID],emp.name EmpName,cg.type,cg.keyid,o.type OrgType,emp.Gender,ed.DutyName as NowDuty
  FROM CheGai cg inner join dbo.Emp_BasicInfo emp on cg.empid = emp.id 
  inner join orgforhr o on o.id = emp.orgid
  left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任' 
  left join zhiji zj on zj.dutylevel = oldzhiji
  left join zhiji zj1 on zj1.dutylevel = ed.dutylevel where cg.id = {0} ", id);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(CheGaiModel.FillEntity(dr));
            }
            if (rights.Count > 0)
                return rights[0];
            else return null;
        }

        public bool UpdateCheGai(CheGaiModel item)
        {
            string sql = string.Format(@"UPDATE CheGai
   SET [EmpID] = {0}
      ,[OldZhiJi] = '{1}'
      ,[OldJinE] = '{2}'
      ,[ChangeDate] = '{3}'
      ,[NewZhiJi] = '{4}'
      ,[NewJinE] = '{5}'
      ,[Operator] = '{6}'
      ,[CreateDate] = '{7}'
      ,[Status] = '{8}'
      ,[Demo] = '{9}'
      ,[RelationID] = {10},type = '{11}',keyid={12},gender = '{13}',nowduty = '{14}'
 WHERE ID = {15}", item.EmpID, item.OldZhiJi, item.OldJinE, item.ChangeDate, item.NewZhiJi, item.NewJinE, item.Operator,
                  item.CreateDate, item.Status, item.Demo, item.RelationID, item.Type, item.KeyID, item.Gender, item.NowDuty,
                 item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateCheGaiAll(ObservableCollection<CheGaiModel> item)
        {
            string ids = "";
            foreach (CheGaiModel i in item)
            {
                ids += i.ID.ToString() + ",";
            }
            ids = ids.Substring(0, ids.Length - 1);
            string sql = string.Format(@"UPDATE CheGai
   SET [Status] = '已导出'
 WHERE ID in {0}", "(" + ids + ")");
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            if (item.Where(p => p.Status == "已导出").FirstOrDefault() == null)
            {
                foreach (CheGaiModel i in item)
                {
                    if (i.Type == "变动表") continue;
                    string chasql =
                        string.Format(
                            @"INSERT INTO CheGai
           ([EmpID] ,[OldZhiJi] ,[Status] ,[Demo] ,[RelationID],[type],CreateDate,KeyID)
           
      SELECT [EmpID] ,[OldZhiJi] ,'未导出' ,[Demo] ,[RelationID],'变动表',GETDATE(),
      case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID FROM chegai WHERE id = {1}",
                            i.EmpID, i.ID);
                    if (!string.IsNullOrEmpty(chasql))
                        AccessHelper.GetInstance().ExecuteNonQuery(chasql);

                }
            }


            return true;
        }

        public ObservableCollection<string> GetKeyIDs(string caozuoriqi, string endcaozuoriqi, string orgid)
        {
            ObservableCollection<string> rights = new ObservableCollection<string>();
            string sql = string.Format(@"SELECT distinct keyid
  FROM CheGai cg inner join dbo.Emp_BasicInfo emp on cg.EmpId = emp.id
  inner join orgforhr o on o.id = emp.orgid where 1=1  ");
            if (orgid != "0") sql += " and o.id in (" + orgid + ")";
            if (!string.IsNullOrEmpty(caozuoriqi) && Convert.ToDateTime(caozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) >= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            if (!string.IsNullOrEmpty(endcaozuoriqi) && Convert.ToDateTime(endcaozuoriqi) != Convert.ToDateTime("1900-01-01")) sql += " and convert(VARCHAR(30),CreateDate,111) <= convert(VARCHAR(30),Convert(datetime,  '" + Convert.ToDateTime(caozuoriqi).ToShortDateString() + "'),111)";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(dr[0].ToString());
            }
            return rights;
        }
        //private byte[] _content = null;
        //public int _id;
        //public FileTransferStream GetExcelFile(ObservableCollection<CheGaiModel> models)
        //{
        //    FileTransferStream ftfs = new FileTransferStream();
        //    if (_content != null)
        //    {
        //    }
        //    else
        //    {
        //        HSSFWorkbook workbook = null;


        //        MemoryStream ms = new MemoryStream(ResourceFile.mianshi);
        //        workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
        //        ms.Dispose();
        //        GetInfo(models, workbook);

        //        string strFileName = AppDomain.CurrentDomain.BaseDirectory +  ".xls";
        //        if (File.Exists(strFileName))
        //        {
        //            File.Delete(strFileName);
        //        }
        //        using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
        //        {
        //            workbook.Write(fs);

        //        }
        //        using (FileStream file = File.Open(strFileName, FileMode.Open))
        //        {
        //            BinaryReader r = new BinaryReader(file);

        //            r.BaseStream.Seek(0, SeekOrigin.Begin);    //将文件指针设置到文件开

        //            _content = r.ReadBytes((int)r.BaseStream.Length);
        //        }
        //        if (File.Exists(strFileName))
        //        {
        //            File.Delete(strFileName);
        //        }

        //        ftfs.IsFinished = true;
        //        ftfs.FileContent = _content;

        //    }
        //    return ftfs;
        //}
        //private DataSet GetInfo(ObservableCollection<CheGaiModel> models, HSSFWorkbook workbook)
        //{
        //    HSSFSheet sheet1 = workbook.GetSheetAt(0);
        //    HSSFCellStyle style = workbook.CreateCellStyle();
        //    style.Alignment = HSSFCellStyle.ALIGN_CENTER;
        //    style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
        //    style.BorderBottom = HSSFCellStyle.BORDER_THIN;
        //    style.BorderLeft = HSSFCellStyle.BORDER_THIN;
        //    style.BorderRight = HSSFCellStyle.BORDER_THIN;
        //    style.BorderTop = HSSFCellStyle.BORDER_THIN;
        //    #region 基本信息

        //    for (int i = 0; i < models.Count; i++)
        //    {
        //        var row = sheet1.CreateRow(i + 3);
        //        row.Height = 20 * 20;
        //        for (int ii = 0; ii < 31; ii++)
        //        {
        //            HSSFCell cell = row.CreateCell(ii);
        //            cell.CellStyle = style;
        //        }
        //        SetCellValue(sheet1, i + 7, 0, (i + 1).ToString());
        //        SetCellValue(sheet1, i + 7, 1, models[i].RiQi != null ? Convert.ToDateTime(models[i].RiQi).ToString("yyyy-MM-dd") : null);
        //        SetCellValue(sheet1, i + 7, 2, models[i].Zu);
        //        SetCellValue(sheet1, i + 7, 3, models[i].Shi);
        //        SetCellValue(sheet1, i + 7, 4, models[i].Department);
        //        SetCellValue(sheet1, i + 7, 5, models[i].Duty);
        //        SetCellValue(sheet1, i + 7, 6, models[i].EmpName);
        //        SetCellValue(sheet1, i + 7, 7, models[i].BiShiScore.ToString());
        //        SetCellValue(sheet1, i + 7, 8, models[i].CardNo);
        //        SetCellValue(sheet1, i + 7, 9, models[i].OrgName);
        //    }
        //    #endregion

        //    return null;
        //}
        //private bool SetCellValue(HSSFCell cell, object value)
        //{
        //    if (value != null)
        //    {
        //        cell.SetCellValue(value.ToString());
        //    }
        //    return true;
        //}
        //private bool SetCellValue(HSSFSheet sheet, int row, int column, string dr)
        //{
        //    HSSFRow hrow = sheet.GetRow(row - 1);
        //    if (hrow != null)
        //    {
        //        HSSFCell cell = hrow.GetCell(column);
        //        if (cell != null)
        //        {
        //            SetCellValue(cell, dr);
        //        }
        //        else
        //        {
        //            return false;
        //        }
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //    return true;
        //}
        #endregion

        #region 组织机构
        public ObservableCollection<OrgForHRModel> GetOrgForHRModel(int OrgID)
        {
            ObservableCollection<OrgForHRModel> rights = new ObservableCollection<OrgForHRModel>();
            string sql = "";
            if (OrgID == 0)
                sql = string.Format(@"SELECT [ID]
      ,[Name]
      ,[Type]
      ,[ParentID]
      ,[OrgNo]
  FROM OrgForHR");
            else
                sql = string.Format(@"SELECT [ID]
      ,[Name]
      ,[Type]
      ,[ParentID]
      ,[OrgNo]
  FROM OrgForHR where ID = {0}", OrgID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(OrgForHRModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<OrgForHRModel> GetOrgForHRModelbyIDs(string OrgID)
        {
            ObservableCollection<OrgForHRModel> rights = new ObservableCollection<OrgForHRModel>();
            string
                sql = @"SELECT [ID]
      ,[Name]
      ,[Type]
      ,[ParentID]
      ,[OrgNo]
  FROM OrgForHR where ID in " + OrgID;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(OrgForHRModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddOrgForHRModel(OrgForHRModel item)
        {
            string sql = string.Format(@"INSERT INTO OrgForHR
           ([Name]
           ,[Type]
           ,[ParentID]
           ,[OrgNo])
     VALUES
           ('{0}'
           ,'{1}'
           ,(select ID from OrgForHR where Name = '{1}')
           ,'{2}')", item.Name, item.Type, item.OrgNo);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateOrgForHRModel(OrgForHRModel item)
        {
            string sql = string.Format(@"UPDATE OrgForHR
   SET [Name] = '{0}'
      ,[Type] = '{1}'
      ,[ParentID] = '{2}'
      ,[OrgNo] = '{3}'
 WHERE ID = {4}", item.Name, item.Type, item.ParentID, item.OrgNo, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeteleOrgForHRModel(int ID)
        {
            string sql = string.Format(@"
delete from OrgForHR where ID = {0}", ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 编制管理
        public ObservableCollection<Emp_BasicInfoModel> GetEmpIds(int bianzhiid, string type, int isguoduxing)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql = string.Format(@"select *,'false' as IsSelected,o.Name OrgName,ed.DutyName,ed.DutyNo from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
inner join OrgForHR o on o.ID = emp.OrgID
where emp.ID in
(select empbasicid from BianZhiJieYongEmp where BianZhiJieYongID in 
(select ID from BianZhiJieYong where BianZhiID = {0} and JieYongType = '{1}' and isguoduxing = '{2}')) and ed.JobStatus = '在任' and EmpStatus = '在职'", bianzhiid, type, isguoduxing);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;
        }

        public bool CanTuiHuanZhiShu(int bianzhijieyongid)
        {
            bool can = true;
            string sql = string.Format(@"select count(id) from BianZhiJieYongEmp where BianZhiJieYongID = {0}", bianzhijieyongid);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0)
                {
                    can = false;
                }
            }
            return can;
        }

        public bool TuiHuanZhiShu(int bianzhijieyongid,int empid)
        {
            string sql = "";
            if(bianzhijieyongid != 0)
                sql = string.Format(@"delete from BianZhiJieYong where ID = {0}", bianzhijieyongid);
            if(empid != 0)
                sql = string.Format(@"delete from BianZhiJieYongEmp where EmpBasicID = {0}", empid);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateBianZhiAnPaiRenYuan(ObservableCollection<int> empid, int bianzhijieyongid, string jieyongorguodu, string type)
        {
            string sql = "";
            bool can = false;
            string ca = "";
            string yanzhengsql;
            foreach (int i in empid)
            {
                ca += i.ToString() + ",";
            }
            //if (!string.IsNullOrEmpty(ca))
            //{
            //    yanzhengsql = "select count(ID) from BianZhiJieYongEmp where EmpBasicID in (" +
            //                      ca.Substring(0, ca.Length - 1) + ")";
            //    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(yanzhengsql);
            //    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            //    {
            //        if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0)
            //        {
            //            can = true;
            //        }
            //    }
            //}

            if (jieyongorguodu == "借用")
            {
                yanzhengsql = @"select count(bzjye.ID) from BianZhiJieYongEmp bzjye inner join BianZhiJieYong b on bzjye.BianZhiJieYongID = b.ID
 where isguoduxing = 1 and isfenpei = 0 and bzjye.EmpBasicID in (" +
                              ca.Substring(0, ca.Length - 1) + ")";
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(yanzhengsql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0)
                    {
                        can = true;
                    }
                }
                if (can == true)
                {
                    return false;
                }
                sql += string.Format(@"declare @ID int
select @ID = id from BianZhiJieYong where BianZhiID = {1} and JieYongType = '{2}' and isguoduxing = 0 and isfenpei = 0
insert into BianZhiJieYongEmp (EmpBasicID,BianZhiJieYongID) values
({0},@ID);update BianZhiJieYong set isfenpei = 1 where ID = {1}", empid[0], bianzhijieyongid, type);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            if (jieyongorguodu == "过渡性")
            {
                yanzhengsql = @"select count(bzjye.ID) from BianZhiJieYongEmp bzjye inner join BianZhiJieYong b on bzjye.BianZhiJieYongID = b.ID
 where isguoduxing = 0 and isfenpei = 0 and bzjye.EmpBasicID in (" +
                              ca.Substring(0, ca.Length - 1) + ")";
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(yanzhengsql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0)
                    {
                        can = true;
                    }
                }
                if (can == true)
                {
                    return false;
                }
                foreach (int i in empid)
                {
                    sql += string.Format(@"declare @ID int
select @ID = id from BianZhiJieYong where BianZhiID = {1} and JieYongType = '{2}' and isguoduxing = 1 and isfenpei = 0
insert into BianZhiJieYongEmp (EmpBasicID,BianZhiJieYongID) values
({0},@ID);update BianZhiJieYong set isfenpei = 1 where ID = {1};", i, bianzhijieyongid, type);
                    if (!string.IsNullOrEmpty(sql))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
                }

            }
            return true;


        }

        public ObservableCollection<Emp_BasicInfoModel> GetEmp_BasicInfoModelByKeShi(int orgid, string type, string keshiname, string zairenjianren, string empid)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql = "";
            if (type == "正科")
            {
                sql = string.Format(@"select *,'false' as IsSelected,o.Name OrgName,ed.DutyName,ed.DutyNo from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
inner join OrgForHR o on o.ID = emp.OrgID
 where OrgID = {0}  and KeChuJi = 0
and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '{1}' and emp.EmpStatus = '在职'", orgid, zairenjianren);
                if (keshiname != "合计")
                    sql += " and ed.DutyName = '" + keshiname + "'";
                if (!string.IsNullOrEmpty(empid))
                    sql += " and emp.ID in (" + empid + ")";
            }
            if (type == "副科")
            {
                sql = string.Format(@"select *,'false' as IsSelected,o.Name OrgName,ed.DutyName,ed.DutyNo from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
inner join OrgForHR o on o.ID = emp.OrgID
  where OrgID = {0} and KeChuJi = 0
and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '{1}' and emp.EmpStatus = '在职'", orgid, zairenjianren);
                if (keshiname != "合计")
                    sql += " and ed.DutyName = '" + keshiname + "'";
                if (!string.IsNullOrEmpty(empid))
                    sql += " and emp.ID in (" + empid + ")";
            }
            if (type == "主任")
            {
                sql = string.Format(@"select *,'false' as IsSelected,o.Name OrgName,ed.DutyName,ed.DutyNo from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
inner join OrgForHR o on o.ID = emp.OrgID
  where OrgID = {0} and KeChuJi = 0
and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '{1}' and emp.EmpStatus = '在职'", orgid, zairenjianren);
                if (keshiname != "合计")
                    sql += " and ed.DutyName = '" + keshiname + "'";
                if (!string.IsNullOrEmpty(empid))
                    sql += " and emp.ID in (" + empid + ")";
            }
            if (type == "副主任")
            {
                sql = string.Format(@"select *,'false' as IsSelected,o.Name OrgName,ed.DutyName,ed.DutyNo from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
inner join OrgForHR o on o.ID = emp.OrgID
  where OrgID = {0} and KeChuJi = 0
and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '{1}' and emp.EmpStatus = '在职'", orgid, zairenjianren);
                if (keshiname != "合计")
                    sql += " and ed.DutyName = '" + keshiname + "'";
                if (!string.IsNullOrEmpty(empid))
                    sql += " and emp.ID in (" + empid + ")";
            }
            if (type == "科员")
            {
                sql = string.Format(@"select *,'false' as IsSelected,o.Name OrgName,ed.DutyName,ed.DutyNo from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
inner join OrgForHR o on o.ID = emp.OrgID
 where OrgID = {0} 
and DutyLevel = '科员' and KeChuJi = 0 and JobStatus = '{1}' and emp.EmpStatus = '在职'", orgid, zairenjianren);
                if (keshiname != "合计")
                    sql += " and ed.DutyName = '" + keshiname + "'";
                if (!string.IsNullOrEmpty(empid))
                    sql += " and emp.ID in (" + empid + ")";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<BianZhiModel> GetBianZhi_JiBenModel(string OrgID)
        {
            if (!string.IsNullOrEmpty(OrgID))
            {
                ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
                string sql = string.Format(@"select distinct b.OrgID,o.Name OrgName,case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end JiGuanXingZheng
,case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end ZhengFaXingZheng,a.canzhaocount,c.xingzhengcount,d.zhengfacount,e.canzhaoshiyoucount,f.chujicount,
g.kejicount,h.keyuancount,i.weidingjicount,j.weiguoducount,k.zongcount,
case when (k.zongcount - (case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end)
- (case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end)
- (case when a.canzhaocount is null then 0 else a.canzhaocount end)) <=0 then '无' else 
CONVERT(varchar,(k.zongcount - (case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end)
- (case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end)
- (case when a.canzhaocount is null then 0 else a.canzhaocount end))) end chaobian ,

case when ((case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end)
- (case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end)
- (case when a.canzhaocount is null then 0 else a.canzhaocount end) - k.zongcount) <=0 then '无' else 
 CONVERT(varchar,((case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end)
- (case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end)
- (case when a.canzhaocount is null then 0 else a.canzhaocount end) - k.zongcount)) end kongbian,

case when 
((case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end) + (case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end)
+ (case when a.canzhaocount is null then 0 else a.canzhaocount end)) > 0 then
(case when ((case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end)
- (case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end)
- (case when a.canzhaocount is null then 0 else a.canzhaocount end) - k.zongcount) <= 0 then 0 else
((case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end)
- (case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end)
- (case when a.canzhaocount is null then 0 else a.canzhaocount end) - k.zongcount) end )/
((case when JiGuanXingZheng is null then 0 else JiGuanXingZheng end) + (case when ZhengFaXingZheng is null then 0 else ZhengFaXingZheng end)
+ (case when a.canzhaocount is null then 0 else a.canzhaocount end)) else 0 end kongbianlv,b.QingKuangMiaoShu
  from BianZhi b left join OrgForHR o on b.OrgID = o.ID
left join 
(select top 1 case when CanZhaoGuanLi IS not null then CanZhaoGuanLi else 0 end canzhaocount,OrgID from BianZhi where OrgID in ({0}) ) a on b.OrgID = a.OrgID
left join
(select COUNT(ID) xingzhengcount,OrgID from Emp_BasicInfo where OrgID in ({0}) and EmpStatus = '在职' and OrganizationType = '行政' group by OrgID) 
c on c.OrgID = b.OrgID
left join
(select COUNT(ID) zhengfacount,OrgID from Emp_BasicInfo where OrgID in ({0}) and EmpStatus = '在职' and OrganizationType = '政法行政'  group by OrgID) 
d on d.OrgID = b.OrgID
left join
(select COUNT(ID) canzhaoshiyoucount,OrgID from Emp_BasicInfo where OrgID in ({0}) and EmpStatus = '在职' and OrganizationType = '参照管理事业' group by OrgID) 
e on e.OrgID = b.OrgID
left join
(select COUNT(ID) chujicount,OrgID from Emp_BasicInfo where OrgID in ({0}) and EmpStatus = '在职' and KeChuJi = 1 group by OrgID) 
f on f.OrgID = b.OrgID
left join
(select COUNT(emp.ID) kejicount,OrgID from Emp_BasicInfo emp inner join emp_duty ed on emp.id = ed.empbasicid
 where OrgID in ({0}) and EmpStatus = '在职' and KeChuJi = 0 and ed.JobStatus = '在任' and DutyLevel in ('乡科级副职（副主任科员）','乡科级正职（主任科员）') group by OrgID) 
g on g.OrgID = b.OrgID
left join
(select COUNT(emp.ID) keyuancount,OrgID from Emp_BasicInfo emp inner join emp_duty ed on emp.id = ed.empbasicid
 where OrgID in ({0}) and EmpStatus = '在职' and KeChuJi = 0 and ed.JobStatus = '在任' and DutyLevel in ('科员') group by OrgID) 
h on h.OrgID = b.OrgID
left join
(select COUNT(emp.ID) weidingjicount,OrgID from Emp_BasicInfo emp inner join emp_duty ed on emp.id = ed.empbasicid
 where OrgID in ({0}) and EmpStatus = '在职' and KeChuJi = 0 and ed.JobStatus = '在任' and DutyLevel in ('未定职') group by OrgID) 
i on i.OrgID = b.OrgID
left join
(select COUNT(ID) weiguoducount,OrgID from Emp_BasicInfo where OrgID in ({0}) and EmpStatus = '长病假未过渡' group by OrgID ) 
j on j.OrgID = b.OrgID
left join
(select COUNT(ID) zongcount,OrgID from Emp_BasicInfo where OrgID in ({0}) and EmpStatus in ('长病假未过渡','在职') group by OrgID ) 
k on k.OrgID = b.OrgID
 where b.OrgID in ({0})", OrgID);
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    rights.Add(BianZhiModel.FillEntity(dr));
                }
                return rights;
            }
            else return null;
        }

        public string GetChaoZhiShu(int bianzhiid, string type)
        {
            string rights = "";
            string sql = string.Format(@"select GuoDuZhiShu from BianZhiJieYong where BianZhiID = {0} and IsGuoDuXing = 1 and JieYongType = '{1}'", bianzhiid, type);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                rights = ds.Tables[0].Rows[0][0].ToString();
            return rights;
        }

        public bool UpdateBianZhi_JiBenModel(int id, string demo)
        {
            string sql = string.Format(@"UPDATE BianZhi set QingKuangMiaoShu = '{0}'
 WHERE OrgID = {1}", demo, id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool InsertChaoZhi(int bianzhiid, string type, string guoduxingzhishu, string bianzhiname)
        {
            string sql = string.Format(@"insert into BianZhiJieYong (BianZhiID ,JieYongQingKuang,JieYongType,IsGuoDuXing,GuoDuZhiShu) values
                ('{0}','过渡性超职','{1}','1','{2}');update BianZhi set caozhiqingkuang = case when chaozhiqingkuang is null then chaozhiqingkuang else
  chaozhiqingkuang + ';' +  '{3}'", bianzhiid, type, guoduxingzhishu, bianzhiname + "过渡性超职：" + guoduxingzhishu);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateChaoZhiQingKuang(int id, string demo)
        {
            string sql = string.Format(@"UPDATE BianZhi set ChaoZhiQingKuang = '{0}'
 WHERE OrgID = {1}", demo, id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public ObservableCollection<BianZhiModel> GetBianZhiDetailModel(string OrgID,string jigoumingcheng)
        {
            if (!string.IsNullOrEmpty(OrgID))
            {
                ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
                string sql = string.Format(@"select distinct  b.ID, o.Name OrgName,o.id OrgID,JiGouMingCheng,ZhengKeLingDao,a.zhengkeshiyou,c.zhengkejianren,i.ZhengKeJieYongQingKuang,b.ZhengKeJieYongDutyID,t.IsZhengKeTongKeShiJieYong,
b.ZhengKeFeiLingDao,d.fukeshiyou,e.fukejianren,j.FuKeJieYongQingKuang,FuKeJieYongDutyID,
b.FuKeLingDao,f.zhurenshiyou,k.ZhuRenJieYongQingKuang,b.ZhuRenJieYongDutyID,
b.FuKeFeiLingDao,g.fuzhurenshiyou,l.FuZhuRenJieYongQingKuang,FuZhuRenJieYongDutyID,h.keyuancount,b.ChaoZhiQingKuang
,p.ZhengKeChaoZhiQingKuang,q.FuKeChaoZhiQingKuang,r.ZhuRenChaoZhiQingKuang,s.FuZhuRenChaoZhiQingKuang,b.ZhengKeLingDaoTC,b.ZhengKeFeiLingDaoTC,b.TCMC,b.TCMC1
 from BianZhi b 
inner join OrgForHR o on b.OrgID = o.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) ZhengKeJieYongQingKuang,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongType = '正科' and OrgID = {0} and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) FuKeJieYongQingKuang,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongQingKuang = '正科' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) i on i.ID = b.ID
left join
(select GuoDuZhiShu ZhengKeChaoZhiQingKuang,BianZhiID  ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongType = '正科' and OrgID = {0} and IsGuoDuXing = 1) p on p.ID = b.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) FuKeJieYongQingKuang,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongType = '副科' and OrgID = {0} and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) ZhengKeJieYongQingKuang,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongQingKuang = '副科' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) j on j.ID = b.ID
left join
(select GuoDuZhiShu FuKeChaoZhiQingKuang,BianZhiID  ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongType = '副科' and OrgID = {0} and IsGuoDuXing = 1) q on q.ID = b.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) ZhuRenJieYongQingKuang,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongType = '主任' and OrgID = {0} and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) ZhuRenJieYongQingKuang,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongQingKuang = '主任' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) k on k.ID = b.ID
left join
(select GuoDuZhiShu ZhuRenChaoZhiQingKuang,BianZhiID  ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongType = '主任' and OrgID = {0} and IsGuoDuXing = 1) r on r.ID = b.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) FuZhuRenJieYongQingKuang,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongType = '副主任' and OrgID = {0} and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) FuZhuRenJieYongQingKuang,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongQingKuang = '副主任' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) l on l.ID = b.ID
left join
(select GuoDuZhiShu FuZhuRenChaoZhiQingKuang,BianZhiID  ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
--inner join BianZhiJieYongEmp bje on bje.BianZhiJieYongID = bj.ID
where JieYongType = '副主任' and OrgID = {0} and IsGuoDuXing = 1) s on s.ID = b.ID
left join 
(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and KeChuJi = 0 and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by ed.DutyName) a
on a.DutyName = b.JiGouMingCheng
left join 
(select COUNT(emp.ID) zhengkejianren,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and KeChuJi = 0 and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '兼任' and emp.EmpStatus = '在职' and KeChuJi = 0
group by ed.DutyName) c
on c.DutyName = b.JiGouMingCheng

left join 
(select COUNT(emp.ID) fukeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and KeChuJi = 0 and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by ed.DutyName) d
on d.DutyName = b.JiGouMingCheng
left join 
(select COUNT(emp.ID) fukejianren,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and KeChuJi = 0 and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '兼任' and emp.EmpStatus = '在职' and KeChuJi = 0
group by ed.DutyName) e
on e.DutyName = b.JiGouMingCheng

left join 
(select COUNT(emp.ID) zhurenshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and KeChuJi = 0 and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by ed.DutyName) f
on f.DutyName = b.JiGouMingCheng
left join 
(select COUNT(emp.ID) fuzhurenshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and KeChuJi = 0 and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by ed.DutyName) g
on g.DutyName = b.JiGouMingCheng
left join 
(select COUNT(emp.ID) keyuancount,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and KeChuJi = 0 and DutyLevel = '科员' and DutyAttribute = '非领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by ed.DutyName) h
on h.DutyName = b.JiGouMingCheng
left join 
(select IsZhengKeTongKeShiJieYong,BianZhiID from BianZhiJieYong  where IsGuoDuXing = 0) t on t.BianZhiID = b.ID
where OrgID = {0}", OrgID);
        if (!string.IsNullOrEmpty(jigoumingcheng)) sql += " and JiGouMingCheng = '" + jigoumingcheng + "'";
                sql += string.Format(@"
union all
select distinct b.OrgID,'',0,'合计', c.ZhengKeLingDao,a.zhengkeshiyou,d.zhengkejianren,r.ZhengKeJieYongQingKuang,null,null,
i.ZhengKeFeiLingDao,f.fukeshiyou,g.fukejianren,s.FuKeJieYongQingKuang,null,
b.FuKeLingDao,j.zhurenshiyou,null,null,b.FuKeFeiLingDao,l.fuzhurenshiyou,null,null,n.keyuancount,'',p.ZhengKeChaoZhiQingKuang,q.FuKeChaoZhiQingKuang,null,null,0,0,null,null from BianZhi b
left join
(select SUM(a.ZhengKeLingDao) ZhengKeLingDao,a.OrgID from 
(select top 1 ZhengKeLingDao,OrgID from BianZhi where OrgID = {0} and ZhengKeLingDaoTC = 1
union all
select SUM(case when ZhengKeLingDao is null then 0 else ZhengKeLingDao end) ZhengKeLingDao,OrgID
 from BianZhi where OrgID ={0} and ZhengKeLingDaoTC = 0 group by OrgID) a group by a.OrgID) c on
c.OrgID = b.OrgID
left join
(select COUNT(emp.ID) zhengkeshiyou,orgid from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by OrgID) a on a.OrgID = b.OrgID
left join
(select COUNT(emp.ID) zhengkejianren,orgid from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '兼任' and emp.EmpStatus = '在职' and KeChuJi = 0
group by OrgID) d on d.OrgID = b.OrgID
--left join
--( select case when SUM(CONVERT(int, case when a.Cou is null then 0 else a.Cou end)) = 0 then null else
-- SUM(CONVERT(int, case when a.Cou is null then 0 else a.Cou end)) end ZhengKeJieYongQingKuang,a.OrgID  from 
-- (select case when jieyongtype = '借出' then  '-' + CONVERT(varchar, COUNT(b.ID)) else 
--CONVERT(varchar, COUNT(b.ID)) end Cou ,b.OrgID from BianZhi b 
-- inner join OrgForHR o on b.OrgID = o.ID 
-- inner join bianzhijieyong bj on b.ID = bj.bianzhiid
-- where bj.jieyongqingkuang = '正科' and OrgID ={0}
-- group by b.ID,bj.jieyongtype,b.OrgID ) a group by a.OrgID)
--e on e.OrgID = b.OrgID

left join
(select SUM(a.ZhengKeFeiLingDao) ZhengKeFeiLingDao,a.OrgID from 
(select top 1 ZhengKeFeiLingDao,OrgID from BianZhi where OrgID = {0} and ZhengKeFeiLingDaoTC = 1
union all
select SUM(case when ZhengKeFeiLingDao is null then 0 else ZhengKeFeiLingDao end) ZhengKeFeiLingDao,OrgID
 from BianZhi where OrgID ={0} and ZhengKeFeiLingDaoTC = 0 group by OrgID) a group by a.OrgID) i on
i.OrgID = b.OrgID
left join
(select COUNT(emp.ID) fukeshiyou,orgid from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by OrgID) f on f.OrgID = b.OrgID
left join
(select COUNT(emp.ID) fukejianren,orgid from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '兼任' and emp.EmpStatus = '在职' and KeChuJi = 0
group by OrgID) g on g.OrgID = b.OrgID
--left join
--( select case when SUM(CONVERT(int, case when a.Cou is null then 0 else a.Cou end)) = 0 then null else
-- SUM(CONVERT(int, case when a.Cou is null then 0 else a.Cou end)) end FuKeJieYongQingKuang,a.OrgID  from 
-- (select case when jieyongtype = '借出' then  '-' + CONVERT(varchar, COUNT(b.ID)) else 
--CONVERT(varchar, COUNT(b.ID)) end Cou ,b.OrgID from BianZhi b 
-- inner join OrgForHR o on b.OrgID = o.ID 
-- inner join bianzhijieyong bj on b.ID = bj.bianzhiid
-- where bj.jieyongqingkuang = '副科' and OrgID ={0}
-- group by b.ID,bj.jieyongtype,b.OrgID ) a group by a.OrgID)
--h on h.OrgID = b.OrgID

left join
(select COUNT(emp.ID) zhurenshiyou,orgid from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by OrgID) j on j.OrgID = b.OrgID
--left join
--( select case when SUM(CONVERT(int, case when a.Cou is null then 0 else a.Cou end)) = 0 then null else
-- SUM(CONVERT(int, case when a.Cou is null then 0 else a.Cou end)) end ZhuRenJieYongQingKuang,a.OrgID  from 
-- (select case when jieyongtype = '借出' then  '-' + CONVERT(varchar, COUNT(b.ID)) else 
--CONVERT(varchar, COUNT(b.ID)) end Cou ,b.OrgID from BianZhi b 
-- inner join OrgForHR o on b.OrgID = o.ID 
-- inner join bianzhijieyong bj on b.ID = bj.bianzhiid
-- where bj.jieyongqingkuang = '主任' and OrgID ={0}
-- group by b.ID,bj.jieyongtype,b.OrgID ) a group by a.OrgID)
--k on k.OrgID = b.OrgID

left join
(select COUNT(emp.ID) fuzhurenshiyou,orgid from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by OrgID) l on l.OrgID = b.OrgID
--left join
--( select case when SUM(CONVERT(int, case when a.Cou is null then 0 else a.Cou end)) = 0 then null else
-- SUM(CONVERT(int, case when a.Cou is null then 0 else a.Cou end)) end FuZhuRenJieYongQingKuang,a.OrgID  from 
-- (select case when jieyongtype = '借出' then  '-' + CONVERT(varchar, COUNT(b.ID)) else 
--CONVERT(varchar, COUNT(b.ID)) end Cou ,b.OrgID from BianZhi b 
-- inner join OrgForHR o on b.OrgID = o.ID 
-- inner join bianzhijieyong bj on b.ID = bj.bianzhiid
-- where bj.jieyongqingkuang = '副主任' and OrgID ={0}
-- group by b.ID,bj.jieyongtype,b.OrgID ) a group by a.OrgID)
--m on m.OrgID = b.OrgID

left join
(select COUNT(emp.ID) keyuancount,orgid from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '科员' and DutyAttribute = '非领导职务' and JobStatus = '在任' and KaoHeStatus != '1' and emp.EmpStatus = '在职' and KeChuJi = 0
group by OrgID) n on n.OrgID = b.OrgID

left join
(select SUM(CONVERT(int, case when GuoDuZhiShu is null then '0' else GuoDuZhiShu end)) ZhengKeChaoZhiQingKuang,OrgID  ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '正科' and OrgID = {0} and IsGuoDuXing = 1
group by OrgID) p on p.ID = b.OrgID
left join
(select SUM(CONVERT(int, case when GuoDuZhiShu is null then '0' else GuoDuZhiShu end)) FuKeChaoZhiQingKuang,OrgID  ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '副科' and OrgID = {0} and IsGuoDuXing = 1
group by OrgID) q on q.ID = b.OrgID
left join
(select SUM(case when r.ZhengKeJieYongQingKuang is null then 0 else r.ZhengKeJieYongQingKuang end) ZhengKeJieYongQingKuang,r.ID from 
(select CONVERT(varchar, COUNT(bj.ID)) ZhengKeJieYongQingKuang,b.OrgID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '正科' and OrgID = {0} and IsGuoDuXing = 0
group by b.OrgID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) ZhengKeJieYongQingKuang,b.OrgID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
where JieYongQingKuang = '正科' and OrgID =  {0} and IsGuoDuXing = 0
group by b.OrgID) r group by r.ID) r on r.ID = b.OrgID
left join
(select SUM(case when r.FuKeJieYongQingKuang is null then 0 else r.FuKeJieYongQingKuang end) FuKeJieYongQingKuang,r.ID from 
(select CONVERT(varchar, COUNT(bj.ID)) FuKeJieYongQingKuang,b.OrgID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '副科' and OrgID = {0} and IsGuoDuXing = 0
group by b.OrgID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) FuKeJieYongQingKuang,b.OrgID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
where JieYongQingKuang = '副科' and OrgID =  {0} and IsGuoDuXing = 0
group by b.OrgID) r group by r.ID) s on s.ID = b.OrgID
where b.OrgID = {0}", OrgID);
                //if (!string.IsNullOrEmpty(jigoumingcheng)) sql += " and JiGouMingCheng = '" + jigoumingcheng + "'";
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    rights.Add(BianZhiModel.FillEntity(dr));
                }
                return rights;
            }
            else return null;
        }

        public ObservableCollection<BianZhiModel> GetBianZhiDetail_YiJieYongModel(int ID, string type, int zhengfu)
        {
            ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
            string sql = "";
            if (zhengfu > 0)
            {
                sql = string.Format(@"select '借入' JieRuJieChu,JiGouMingCheng ,a.JieYongQingKuang ZhengKeJieYongQingKuang,a.ID from BianZhi b inner join
(select JieYongID,JieYongQingKuang,ID from BianZhiJieYong where BianZhiID = {0} and JieYongType = '{1}') 
a on b.ID = a.JieYongID", ID, type);
                //                sql = string.Format(@"declare @str varchar(100)
                //select @str = ZhengKeJieYongDutyID from BianZhi where ID = {0}
                //select (JiGouMingCheng + '-正科职数') JiGouMingCheng from BianZhi where charindex(','+ltrim(id)+',',','+@str+',') > 0", ID);

            }
            if (zhengfu < 0)
            {
                sql = string.Format(@"select '借出' JieRuJieChu, JiGouMingCheng ,a.JieYongType ZhengKeJieYongQingKuang,a.ID from BianZhi b inner join
(select BianZhiID,JieYongType,ID from BianZhiJieYong where JieYongID = {0} and JieYongQingKuang = '{1}') 
a on b.ID = a.BianZhiID", ID, type);

            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(BianZhiModel.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<BianZhiModel> GetBianZhiDetail_JieYongModel(string OrgID, string type)
        {
            ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
            string sql = "";
            if (type == "正科借用" || type == "主任借用")
            {
                sql = string.Format(@"select b.id, (b.JiGouMingCheng + '-正科职数') JiGouMingCheng,
(CONVERT(int, b.ZhengKeLingDao) - CONVERT(int,case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end) + 
CONVERT(int,case when jieyong is null then 0 else jieyong end)) CanUse,
a.zhengkeshiyou,'正科职数' Demo,c.jieyong  from BianZhi b 
inner join OrgForHR o on b.OrgID = o.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) jieyong,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '正科' and OrgID = {0} and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) jieyong,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
where JieYongQingKuang = '正科' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID ) c on c.ID = b.ID
left join 
(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
where OrgID = {0} and ZhengKeLingDao - case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end 
--+ case when jieyong is null then 0 else jieyong end
> 0", OrgID);
            }
            if (type == "副科借用")
            {
                sql = string.Format(@"select b.id, (b.JiGouMingCheng + '-正科职数') JiGouMingCheng,
(CONVERT(int, b.ZhengKeLingDao) - CONVERT(int,case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end) + 
CONVERT(int,case when jieyong is null then 0 else jieyong end)) CanUse,
a.zhengkeshiyou,'正科职数' Demo,c.jieyong  from BianZhi b 
inner join OrgForHR o on b.OrgID = o.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) jieyong,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '正科' and OrgID = {0} and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) jieyong,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
where JieYongQingKuang = '正科' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) c on c.ID = b.ID
left join 
(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
where OrgID = {0} and ZhengKeLingDao - case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end 
--+ case when jieyong is null then 0 else jieyong end
> 0
union all
select b.id, (b.JiGouMingCheng + '-副科职数') JiGouMingCheng,
(CONVERT(int, b.ZhengKeFeiLingDao) - CONVERT(int,case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end) + 
CONVERT(int,case when jieyong is null then 0 else jieyong end)) CanUse,
a.zhengkeshiyou,'副科职数' Demo,c.jieyong  from BianZhi b 
inner join OrgForHR o on b.OrgID = o.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) jieyong,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '副科' and OrgID = {0} and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) jieyong,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
where JieYongQingKuang = '副科' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) c on c.ID = b.ID
left join 
(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
where OrgID = {0} and ZhengKeFeiLingDao - case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end 
--+ case when jieyong is null then 0 else jieyong end
> 0
", OrgID);
            }
            if (type == "副主任借用")
            {
                sql = string.Format(@"select b.id, (b.JiGouMingCheng + '-正科职数') JiGouMingCheng,
(CONVERT(int, b.ZhengKeLingDao) - CONVERT(int,case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end) + 
CONVERT(int,case when jieyong is null then 0 else jieyong end)) CanUse,
a.zhengkeshiyou,'正科职数' Demo,c.jieyong  from BianZhi b 
inner join OrgForHR o on b.OrgID = o.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) jieyong,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '正科' and OrgID = {0} and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) jieyong,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
where JieYongQingKuang = '正科' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) c on c.ID = b.ID
left join 
(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
where OrgID = {0} and ZhengKeLingDao - case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end 
--+ case when jieyong is null then 0 else jieyong end
> 0
union all

select b.id, (b.JiGouMingCheng + '-副科职数') JiGouMingCheng,
(CONVERT(int, b.ZhengKeFeiLingDao) - CONVERT(int,case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end) + 
CONVERT(int,case when jieyong is null then 0 else jieyong end)) CanUse,
a.zhengkeshiyou,'副科职数' Demo,c.jieyong  from BianZhi b 
inner join OrgForHR o on b.OrgID = o.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) jieyong,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '副科' and OrgID = {0}  and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) jieyong,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
where JieYongQingKuang = '副科' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) c on c.ID = b.ID
left join 
(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
where OrgID = {0} and ZhengKeFeiLingDao - case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end 
--+ case when jieyong is null then 0 else jieyong end
> 0

union all
select a.ID,a.JiGouMingCheng,(select CONVERT(int, case when (select top 1 FuKeLingDao from BianZhi where OrgID = {0}) IS null then '0' else 
	(select top 1 FuKeLingDao from BianZhi where OrgID = {0}) end) -
	(
	select COUNT(emp.ID) zhengkeshiyou from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
) )  CanUse,a.zhengkeshiyou, '主任职数'  Demo,a.jieyong
from 
(select b.id, (b.JiGouMingCheng + '-主任职数') JiGouMingCheng,f.FuKeLingDao,
a.zhengkeshiyou,'主任职数' Demo,c.jieyong  from BianZhi b 
inner join OrgForHR o on b.OrgID = o.ID
left join
(select CONVERT(varchar, COUNT(bj.ID)) jieyong,BianZhiID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.BianZhiID
where JieYongType = '主任' and OrgID = {0}  and IsGuoDuXing = 0
group by BianZhiID
union all 
select '-' + CONVERT(varchar, COUNT(bj.ID)) jieyong,JieYongID ID from BianZhi b 
inner join BianZhiJieYong bj on b.ID = bj.JieYongID
where JieYongQingKuang = '主任' and OrgID =  {0} and IsGuoDuXing = 0
group by JieYongID) c on c.ID = b.ID
left join
(select top 1 FuKeLingDao,OrgID from BianZhi where OrgID = {0} )  f on f.OrgID = b.OrgID
left join 
(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
where b.OrgID = {0} and f.FuKeLingDao - case when a.zhengkeshiyou is null then 0 else a.zhengkeshiyou end 
--+ case when jieyong is null then 0 else jieyong end
> 0  ) a
where (select CONVERT(int, case when (select top 1 FuKeLingDao from BianZhi where OrgID = {0}) IS null then '0' else 
	(select top 1 FuKeLingDao from BianZhi where OrgID = {0}) end) -
	(
	select COUNT(emp.ID) zhengkeshiyou from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
) ) > 0
", OrgID);
            }
            //            if (type == "主任借用")
            //            {
            //                sql = string.Format(@"select b.id, (b.JiGouMingCheng + '-正科职数') JiGouMingCheng,(CONVERT(int, b.ZhengKeLingDao) - CONVERT(int,a.zhengkeshiyou)) CanUse,'正科职数' Demo from BianZhi b 
            //inner join OrgForHR o on b.OrgID = o.ID
            //left join 
            //(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
            //where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
            //group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
            //where OrgID = {0} and ZhengKeLingDao > a.zhengkeshiyou
            //
            //union all
            //select b.id, (b.JiGouMingCheng + '-副科职数') JiGouMingCheng,(CONVERT(int, b.ZhengKeFeiLingDao) - CONVERT(int,a.zhengkeshiyou)) CanUse,'副科职数' Demo from BianZhi b 
            //inner join OrgForHR o on b.OrgID = o.ID
            //left join 
            //(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
            //where OrgID = {0} and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
            //group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
            //where OrgID = {0} and ZhengKeFeiLingDao > a.zhengkeshiyou
            //
            //union all
            //select b.id, (b.JiGouMingCheng + '-主任科员职数') JiGouMingCheng,(CONVERT(int, b.FuKeLingDao) - CONVERT(int,f.zhurenshiyou)) CanUse,'主任科员职数' Demo from BianZhi b 
            //inner join OrgForHR o on b.OrgID = o.ID
            //left join 
            //(select COUNT(emp.ID) zhurenshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
            //where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
            //group by ed.DutyName) f
            //on f.DutyName = b.JiGouMingCheng
            //where OrgID = {0} and FuKeLingDao > f.zhurenshiyou", OrgID);
            //            }
            //            if (type == "副主任借用")
            //            {
            //                sql = string.Format(@"select b.id, (b.JiGouMingCheng + '-正科职数') JiGouMingCheng,(CONVERT(int, b.ZhengKeLingDao) - CONVERT(int,a.zhengkeshiyou)) CanUse,'正科职数' Demo from BianZhi b 
            //inner join OrgForHR o on b.OrgID = o.ID
            //left join 
            //(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
            //where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
            //group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
            //where OrgID = {0} and ZhengKeLingDao > a.zhengkeshiyou
            //
            //union all
            //select b.id, (b.JiGouMingCheng + '-副科职数') JiGouMingCheng,(CONVERT(int, b.ZhengKeFeiLingDao) - CONVERT(int,a.zhengkeshiyou)) CanUse,'副科职数' Demo from BianZhi b 
            //inner join OrgForHR o on b.OrgID = o.ID
            //left join 
            //(select COUNT(emp.ID) zhengkeshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
            //where OrgID = {0} and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
            //group by ed.DutyName) a on a.DutyName = b.JiGouMingCheng
            //where OrgID = {0} and ZhengKeFeiLingDao > a.zhengkeshiyou
            //
            //union all
            //select b.id, (b.JiGouMingCheng + '-主任科员职数') JiGouMingCheng,(CONVERT(int, b.FuKeLingDao) - CONVERT(int,f.zhurenshiyou)) CanUse,'主任科员职数' Demo from BianZhi b 
            //inner join OrgForHR o on b.OrgID = o.ID
            //left join 
            //(select COUNT(emp.ID) zhurenshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
            //where OrgID = {0} and DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
            //group by ed.DutyName) f
            //on f.DutyName = b.JiGouMingCheng
            //where OrgID = {0} and FuKeLingDao > f.zhurenshiyou
            //
            //union all
            //select b.id, (b.JiGouMingCheng + '-副主任科员职数') JiGouMingCheng,(CONVERT(int, b.FuKeFeiLingDao) - CONVERT(int,g.fuzhurenshiyou)) CanUse,'副主任科员职数' Demo from BianZhi b 
            //inner join OrgForHR o on b.OrgID = o.ID
            //left join 
            //(select COUNT(emp.ID) fuzhurenshiyou,ed.DutyName from Emp_BasicInfo emp inner join Emp_Duty ed on emp.ID = ed.EmpbasicID
            //where OrgID = {0} and DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '非领导职务' and JobStatus = '在任' and emp.EmpStatus = '在职'
            //group by ed.DutyName) g
            //on g.DutyName = b.JiGouMingCheng
            //where OrgID = {0} and FuKeFeiLingDao > g.fuzhurenshiyou", OrgID);}

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(BianZhiModel.FillEntity(dr));
            }
            return rights;
        }

        public void UpdateBianZhiDetail_JieYongModel(string BianZhiID, string type, BianZhiModel model, int isguoduxing, string guoduxing, bool isnew)
        {
            string sql = "";
            if (type == "正科借用")
            {
                //                sql = string.Format(@"update BianZhi set ZhengKeJieYongQingKuang  = 
                //case when ZhengKeJieYongQingKuang Is null then 0 else ZhengKeJieYongQingKuang end + 1,
                //ZhengKeJieYongDutyID = case when ZhengKeJieYongDutyID is null then '{1}' else 
                //ZhengKeJieYongDutyID + ',' + '{1}' end  where ID = {0};
                //update BianZhi set ZhengKeJieYongQingKuang  = 
                //case when ZhengKeJieYongQingKuang Is null then 0 else ZhengKeJieYongQingKuang end - 1,
                //ZhengKeJieYongDutyID = case when ZhengKeJieYongDutyID is null then '{0}' else 
                //ZhengKeJieYongDutyID + ',' + '{0}' end  where ID = {1};", BianZhiID, model.ID);
                string strGUID = System.Guid.NewGuid().ToString();
                int i = 0;
                if (BianZhiID == model.ID.ToString()) i = 1;
                sql = string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongQingKuang,JieYongID,JieYongType,BianZhiGUID,IsZhengKeTongKeShiJieYong,IsGuoDuXing,GuoDuZhiShu) values
                    ({0},'正科','{1}','正科','{2}','{3}','{4}','{5}');", BianZhiID, model.ID, strGUID, i, isguoduxing, guoduxing);
                if (isguoduxing == 1 && isnew == true)
                {
                    sql = string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongType,BianZhiGUID,IsGuoDuXing,GuoDuZhiShu) values
                    ({0},'正科','{1}','{2}','{3}');", BianZhiID, strGUID, isguoduxing, guoduxing);
                    sql += string.Format(@";update BianZhi set ChaoZhiQingKuang = case when chaozhiqingkuang is null then chaozhiqingkuang else
  chaozhiqingkuang  + ';' +  '{0}' end where orgid = {1}", model.JiGouMingCheng + "-过渡性超职：" + guoduxing, model.OrgID);
                }
                if (isguoduxing == 1 && isnew == false)
                {
                    if (guoduxing != "0")
                        sql = string.Format(@"update BianZhiJieYong set GuoDuZhiShu = '{1}' where BianZhiID = {0} and IsGuoDuXing = 1 and JieYongType = '正科';",
                            BianZhiID, guoduxing);
                    else
                        sql = string.Format(@"delete from BianZhiJieYong  where BianZhiID = {0} and IsGuoDuXing = 1 and JieYongType = '正科';",
                        BianZhiID);
                }
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            if (type == "副科借用" || type == "主任借用" || type == "副主任借用")
            {
                string strGUID = System.Guid.NewGuid().ToString();
                int i = 0;
                string de = "";
                if (!string.IsNullOrEmpty(model.JiGouMingChengS) && model.JiGouMingChengS.Split('-')[1] == "正科职数")
                {
                    de = "正科";
                }
                if (!string.IsNullOrEmpty(model.JiGouMingChengS) && model.JiGouMingChengS.Split('-')[1] == "副科职数")
                {
                    de = "副科";
                }
                if (!string.IsNullOrEmpty(model.JiGouMingChengS) && model.JiGouMingChengS.Split('-')[1] == "主任职数")
                {
                    de = "主任";
                }
                string de1 = "";
                if (type == "副科借用")
                {
                    de1 = "副科";
                }
                if (type == "主任借用")
                {
                    de1 = "主任";
                }
                if (type == "副主任借用")
                {
                    de1 = "副主任";
                }
                if (type == "副科借用" && BianZhiID == model.ID.ToString()) i = 1;
                sql = string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongQingKuang,JieYongID,JieYongType,BianZhiGUID,IsZhengKeTongKeShiJieYong,IsGuoDuXing,GuoDuZhiShu) values
                    ({0},'{4}','{1}','{5}','{2}','{3}','{6}','{7}');", BianZhiID, model.ID, strGUID, i, de, de1, isguoduxing, guoduxing);
                if (isguoduxing == 1 && isnew == true)
                {
                    sql = string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongType,BianZhiGUID,IsGuoDuXing,GuoDuZhiShu) values
                    ({0},'{4}','{1}','{2}','{3}');", BianZhiID, strGUID, isguoduxing, guoduxing, de1);
                    sql += string.Format(@";update BianZhi set ChaoZhiQingKuang = case when chaozhiqingkuang is null then chaozhiqingkuang else
  chaozhiqingkuang end + ';' +  '{0}'  where orgid = {1}", model.JiGouMingCheng + "-过渡性超职：" + guoduxing, model.OrgID);
                    if (type == "主任借用")
                    {
                        sql = string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongType,IsGuoDuXing,GuoDuZhiShu)
select ID,'主任',1,'{0}' from BianZhi where OrgID = {1}", guoduxing, model.OrgID);
                        sql += string.Format(@";update BianZhi set ChaoZhiQingKuang = case when chaozhiqingkuang is null then chaozhiqingkuang else
  chaozhiqingkuang end + ';' +  '{0}'  where orgid = {1}", "主任科员-过渡性超职：" + guoduxing, model.OrgID);
                    }
                    if (type == "副主任借用")
                    {
                        sql = string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongType,IsGuoDuXing,GuoDuZhiShu)
select ID,'副主任',1,'{0}' from BianZhi where OrgID = {1}", guoduxing, model.OrgID);
                        sql += string.Format(@";update BianZhi set ChaoZhiQingKuang = case when chaozhiqingkuang is null then chaozhiqingkuang else
  chaozhiqingkuang end + ';' +  '{0}'  where orgid = {1}", "副主任科员-过渡性超职：" + guoduxing, model.OrgID);
                    }
                }
                if (isguoduxing == 1 && isnew == false)
                {
                    if (guoduxing != "0")
                        sql = string.Format(@"update BianZhiJieYong set GuoDuZhiShu = '{1}' where BianZhiID = {0} and IsGuoDuXing = 1 and JieYongType = '{2}';",
                            BianZhiID, guoduxing, de1);
                    else
                        sql = string.Format(@"delete from BianZhiJieYong  where BianZhiID = {0} and IsGuoDuXing = 1 and JieYongType = '{1}';",
                        BianZhiID, de1);
                    if (type == "主任借用")
                    {
                        if (guoduxing != "0")
                            sql = string.Format(@"update BianZhiJieYong set GuoDuZhiShu = '{0}' where 
BianZhiID in (select ID from BianZhi where OrgID = {1})
and IsGuoDuXing = 1 and JieYongType = '主任';",
                        guoduxing, model.OrgID);
                        else
                            sql = string.Format(@"delete from BianZhiJieYong where 
BianZhiID in (select ID from BianZhi where OrgID = {0})
and IsGuoDuXing = 1 and JieYongType = '主任';",
                        model.OrgID);
                    }
                    if (type == "副主任借用")
                    {
                        if (guoduxing != "0")
                            sql = string.Format(@"update BianZhiJieYong set GuoDuZhiShu = '{0}' where 
BianZhiID in (select ID from BianZhi where OrgID = {1})
and IsGuoDuXing = 1 and JieYongType = '副主任';",
                            guoduxing, model.OrgID);
                        else
                            sql = string.Format(@"delete from BianZhiJieYong where 
BianZhiID in (select ID from BianZhi where OrgID = {0})
and IsGuoDuXing = 1 and JieYongType = '副主任';",
                        model.OrgID);
                    }
                }
                //BianZhiID的JieYongType借用JieYongID的JieYongQingKuang
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            //            if (type == "主任借用")
            //            {
            //                string strGUID = System.Guid.NewGuid().ToString();
            //                int i = 0;
            //                if (BianZhiID == model.ID.ToString()) i = 1;
            //                sql = string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongQingKuang,JieYongID,JieYongType,BianZhiGUID,IsZhengKeTongKeShiJieYong) values
            //                    ({0},'主任','{1}','借用','{2}','{3}');", BianZhiID, model.ID, strGUID, i);
            //                if (model.Demo == "正科职数")
            //                {
            //                    sql += string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongQingKuang,JieYongID,JieYongType,BianZhiGUID,IsZhengKeTongKeShiJieYong) values
            //                    ({1},'正科','{0}','借出','{2}','{3}');", BianZhiID, model.ID, strGUID, i);
            //                }
            //                if (model.Demo == "副科职数")
            //                {
            //                    sql += string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongQingKuang,JieYongID,JieYongType,BianZhiGUID,IsZhengKeTongKeShiJieYong) values
            //                    ({1},'副科','{0}','借出','{2}','{3}');", BianZhiID, model.ID, strGUID, i);
            //                }
            //                if (!string.IsNullOrEmpty(sql))
            //                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            //            }
            //            if (type == "副主任借用")
            //            {
            //                string strGUID = System.Guid.NewGuid().ToString();
            //                int i = 0;
            //                if (BianZhiID == model.ID.ToString()) i = 1;
            //                sql = string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongQingKuang,JieYongID,JieYongType,BianZhiGUID,IsZhengKeTongKeShiJieYong) values
            //                    ({0},'副主任','{1}','借用','{2}','{3}');", BianZhiID, model.ID, strGUID, i);
            //                if (model.Demo == "正科职数")
            //                {
            //                    sql += string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongQingKuang,JieYongID,JieYongType,BianZhiGUID,IsZhengKeTongKeShiJieYong) values
            //                    ({1},'正科','{0}','借出','{2}','{3}');", BianZhiID, model.ID, strGUID, i);
            //                }
            //                if (model.Demo == "副科职数")
            //                {
            //                    sql += string.Format(@"insert into BianZhiJieYong (BianZhiID,JieYongQingKuang,JieYongID,JieYongType,BianZhiGUID,IsZhengKeTongKeShiJieYong) values
            //                    ({1},'副科','{0}','借出','{2}','{3}');", BianZhiID, model.ID, strGUID, i);
            //                }
            //                if (!string.IsNullOrEmpty(sql))
            //                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
            //            }
        }


        public ObservableCollection<BianZhiModel> GetBianZhiModel(string OrgID, int ID)
        {
            ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
            string sql = "";
            if (ID == 0)
            {
                if (!string.IsNullOrEmpty(OrgID))                                                 
                    sql =
                        string.Format(
                            @"SELECT o.Name OrgName, bi.[ID],bi.QingKuangMiaoShu
      ,bi.[OrgID]
      ,[ZongBianZhi]
      ,[ZhengKeLingDao]
      ,[ZhengKeFeiLingDao]
      ,[FuKeLingDao]
      ,[FuKeFeiLingDao]
      ,[KeYuan]
      ,[WeiDingZhi],ChuJiRenShu,KeYiXiaRenShu,JiGouMingCheng,JiGuanXingZheng,ZhengFaXingZheng,CanZhaoGuanLi,ZhengKeLingDaoTC,ZhengKeFeiLingDaoTC,TCMC,TCMC1,
      CASE WHEN (ISNULL((SELECT ZhengKeLingDao FROM dbo.BianZhi WHERE OrgID in ({0}) AND JiGouMingCheng = '司法所'),0)!= 0
      OR ISNULL((SELECT ZhengKeFeiLingDao FROM dbo.BianZhi WHERE OrgID in ({0}) AND JiGouMingCheng = '司法所'),0)!= 0)
      
      THEN (
      CONVERT(varchar, (SELECT SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0))) FROM dbo.BianZhi WHERE OrgID in ({0}) 
       )) + '='
      + CONVERT(varchar, (SELECT SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0)))   FROM dbo.BianZhi 
      WHERE OrgID in ({0}) AND JiGouMingCheng != '司法所') + 
      (SELECT (SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0)))) FROM dbo.BianZhi WHERE OrgID in ({0}) AND JiGouMingCheng = '司法所')) )
      ELSE CONVERT(varchar, (SELECT SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0)))   FROM dbo.BianZhi WHERE OrgID in ({0}) )) END ShiZhiHeJi
,a.ZongShu,b.XiangKeZhengFuZ,f.KeYuanShiYou,g.WeiDingZhiShiYou,bi.ChuJiRenShu 
  FROM BianZhi bi right join OrgForHR o on bi.orgid = o.ID 
LEFT JOIN 
  (select case when COUNT(distinct ID) is null then 0 else COUNT(distinct ID) end ZongShu ,orgid OrgID from Emp_BasicInfo where empstatus = '在职' and orgID in ({0}) group by orgid) a on o.ID = a.OrgID left join
(select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end XiangKeZhengFuZ,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID in ({0}) and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel != '科员' AND d.DutyLevel != '未定职' group by orgid) b on b.OrgID = o.ID  left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end KeYuanShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID in ({0}) and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '科员'  group by orgid) f on f.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end WeiDingZhiShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID in ({0}) and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '未定职'  group by orgid) g on g.OrgID = o.ID
  
where o.ID in ({0})",
                            OrgID);
                else
                    sql =
                        string.Format(
                            @"SELECT o.Name OrgName, bi.[ID],bi.QingKuangMiaoShu
      ,bi.[OrgID]
      ,[ZongBianZhi]
      ,[ZhengKeLingDao]
      ,[ZhengKeFeiLingDao]
      ,[FuKeLingDao]
      ,[FuKeFeiLingDao]
      ,[KeYuan]
      ,[WeiDingZhi],ChuJiRenShu,KeYiXiaRenShu,JiGouMingCheng,JiGuanXingZheng,ZhengFaXingZheng,CanZhaoGuanLi,ZhengKeLingDaoTC,ZhengKeFeiLingDaoTC,TCMC,TCMC1,
      CASE WHEN (ISNULL((SELECT top 1 ZhengKeLingDao FROM dbo.BianZhi WHERE OrgID >0 AND JiGouMingCheng = '司法所'),0)!= 0
      OR ISNULL((SELECT top 1 ZhengKeFeiLingDao FROM dbo.BianZhi WHERE OrgID >0 AND JiGouMingCheng = '司法所'),0)!= 0)
      

      THEN (
      CONVERT(varchar, (SELECT SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0))) FROM dbo.BianZhi WHERE OrgID >0 
       )) + '='
      + CONVERT(varchar, (SELECT SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0)))   FROM dbo.BianZhi 
      WHERE OrgID >0 AND JiGouMingCheng != '司法所') + 
      (SELECT (SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0)))) FROM dbo.BianZhi WHERE OrgID >0 AND JiGouMingCheng = '司法所')) )
      ELSE CONVERT(varchar, (SELECT SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0)))   FROM dbo.BianZhi WHERE OrgID >0 )) END ShiZhiHeJi

  FROM BianZhi bi right join OrgForHR o on bi.orgid = o.ID 
where o.ID >0");
            }
            else
                sql = string.Format(@"SELECT o.Name OrgName, bi.[ID],bi.QingKuangMiaoShu
      ,bi.[OrgID]
      ,[ZongBianZhi]
      ,[ZhengKeLingDao]
      ,[ZhengKeFeiLingDao]
      ,[FuKeLingDao]
      ,[FuKeFeiLingDao]
      ,[KeYuan]
      ,[WeiDingZhi],ChuJiRenShu,KeYiXiaRenShu,JiGouMingCheng,JiGuanXingZheng,ZhengFaXingZheng,CanZhaoGuanLi,ZhengKeLingDaoTC,ZhengKeFeiLingDaoTC,TCMC,TCMC1,
      CASE WHEN (ISNULL((SELECT ZhengKeLingDao FROM dbo.BianZhi WHERE OrgID = 
      (SELECT OrgID FROM dbo.BianZhi WHERE id = {0}) AND JiGouMingCheng = '司法所'),0)!= 0
      OR ISNULL((SELECT ZhengKeFeiLingDao FROM dbo.BianZhi WHERE OrgID = 
      (SELECT OrgID FROM dbo.BianZhi WHERE id = {0}) AND JiGouMingCheng = '司法所'),0)!= 0)

      THEN ((SELECT CONVERT(varchar,(SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0))))) FROM dbo.BianZhi WHERE OrgID = 
      (SELECT OrgID FROM dbo.BianZhi WHERE id = {0}) ) + '='
      + (SELECT CONVERT(varchar,(SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0)))))   FROM dbo.BianZhi 
      WHERE OrgID = (SELECT OrgID FROM dbo.BianZhi WHERE id = {0}) AND JiGouMingCheng != '司法所') + 
      (SELECT CONVERT(varchar,(SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0))))) FROM dbo.BianZhi WHERE OrgID = (SELECT OrgID FROM dbo.BianZhi WHERE id = {0}) AND JiGouMingCheng = '司法所')) 
      ELSE (SELECT CONVERT(varchar,SUM(CONVERT(INT,ISNULL(ZhengKeLingDao,0))) + SUM(CONVERT(INT,ISNULL(ZhengKeFeiLingDao,0))))   FROM dbo.BianZhi WHERE OrgID = (SELECT OrgID FROM dbo.BianZhi WHERE id = {0}) ) END ShiZhiHeJi
  FROM BianZhi bi right join OrgForHR o on bi.orgid = o.ID where bi.ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(BianZhiModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<BianZhiModel> GetBianZhiModelForTongChou(int OrgID)
        {
            ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
            string sql = "";
            sql = string.Format(@"SELECT o.Name OrgName, bi.[ID],bi.QingKuangMiaoShu
      ,[OrgID]
      ,[FuKeLingDao]
      ,[FuKeFeiLingDao],JiGouMingCheng,JiGuanXingZheng,ZhengFaXingZheng,ZhengKeLingDaoTC,TCMC,ZhengKeFeiLingDaoTC,TCMC1
  FROM BianZhi bi right join OrgForHR o on bi.orgid = o.ID where OrgID = {0}", OrgID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(BianZhiModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddBianZhiModel(BianZhiModel item)
        {
            string sql = string.Format(@"INSERT INTO BianZhi
           ([OrgID]
           ,[ZongBianZhi]
           ,[ZhengKeLingDao]
           ,[ZhengKeFeiLingDao]
           ,[FuKeLingDao]
           ,[FuKeFeiLingDao]
           ,[KeYuan]
           ,[WeiDingZhi],ChuJiRenShu,KeYiXiaRenShu,JiGouMingCheng,JiGuanXingZheng,ZhengFaXingZheng,CanZhaoGuanLi,ZhengKeLingDaoTC,ZhengKeFeiLingDaoTC,TCMC,TCMC1,QingKuangMiaoShu)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}','{8}'
           ,'{9}','{10}'
           ,'{11}','{12}'
           ,'{13}','{14}','{15}','{16}','{17}','{18}');UPDATE  dbo.BianZhi SET JiGuanXingZheng = '{11}',ZhengFaXingZheng = '{12}',FuKeLingDao = '{4}',FuKeFeiLingDao = '{5}',
 CanZhaoGuanLi = '{13}'  WHERE OrgID = {0}", item.OrgID, item.ZongBianZhi, item.ZhengKeLingDao, item.ZhengKeFeiLingDao
                   , item.FuKeLingDao, item.FuKeFeiLingDao, item.KeYuan, item.WeiDingZhi, item.ChuJiRenShu, item.KeYiXiaRenShu
                   , item.JiGouMingCheng, item.JiGuanXingZheng, item.ZhengFaXingZheng, item.CanZhaoGuanLi, item.ZhengKeLingDaoTC, item.ZhengKeFeiLingDaoTC, item.TCMC, item.TCMC1, item.QingKuangMiaoShu);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateBianZhiModel(BianZhiModel item)
        {
            string sql = string.Format(@"UPDATE BianZhi
   SET [OrgID] = {0}
      ,[ZongBianZhi] = '{1}'
      ,[ZhengKeLingDao] = '{2}'
      ,[ZhengKeFeiLingDao] = '{3}'
      ,[FuKeLingDao] = '{4}'
      ,[FuKeFeiLingDao] = '{5}'
      ,[KeYuan] = '{6}'
      ,[WeiDingZhi] = '{7}',[ChuJiRenShu] = '{8}'
      ,[KeYiXiaRenShu] = '{9}',JiGouMingCheng= '{11}',JiGuanXingZheng= '{12}',ZhengFaXingZheng= '{13}',CanZhaoGuanLi= '{14}'
        ,ZhengKeLingDaoTC= '{15}',ZhengKeFeiLingDaoTC= '{16}',TCMC = '{17}',TCMC1 = '{18}',QingKuangMiaoShu = '{19}'
 WHERE ID = {10};UPDATE  dbo.BianZhi SET JiGuanXingZheng = '{12}',ZhengFaXingZheng = '{13}',FuKeLingDao = '{4}',FuKeFeiLingDao = '{5}',CanZhaoGuanLi = '{14}'
   WHERE OrgID = {0}", item.OrgID, item.ZongBianZhi, item.ZhengKeLingDao, item.ZhengKeFeiLingDao
                   , item.FuKeLingDao, item.FuKeFeiLingDao, item.KeYuan, item.WeiDingZhi, item.ChuJiRenShu, item.KeYiXiaRenShu, item.ID
                   , item.JiGouMingCheng, item.JiGuanXingZheng, item.ZhengFaXingZheng, item.CanZhaoGuanLi, item.ZhengKeLingDaoTC, item.ZhengKeFeiLingDaoTC, item.TCMC, item.TCMC1, item.QingKuangMiaoShu);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DeleteBianZhiModel(BianZhiModel item)
        {
            string sql = string.Format(@"delete from BianZhi
 WHERE ID = {0}", item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool CanTCXZ(string type, string ids)
        {
            bool can = true;
            string sql = "";
            if (type == "乡科级正职核定数")
                sql = string.Format(@"SELECT ZhengKeLingDaoTC FROM dbo.BianZhi WHERE id IN (" + ids + ")");
            else
                sql = string.Format(@"SELECT ZhengKeFeiLingDaoTC FROM dbo.BianZhi WHERE id IN (" + ids + ")");
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (Convert.ToBoolean(ds.Tables[0].Rows[0][0]) == true)
                    can = false;

            }
            return can;
        }
        public bool TCXZ(string type, int shu, string ids, string name)
        {
            string sql = "";
            if (type == "乡科级正职核定数")
                sql = string.Format(@"UPDATE dbo.BianZhi SET TCMC = '{0}',ZhengKeLingDaoTC = 1,ZhengKeLingDao = '{1}' WHERE id IN (" + ids + ")", name, shu);
            else
                sql = string.Format(@"UPDATE dbo.BianZhi SET TCMC1 = '{0}',ZhengKeFeiLingDaoTC = 1,ZhengKeFeiLingDao = '{1}' WHERE id IN (" + ids + ")", name, shu);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool QuXiaoTongChou(string type, int orgid)
        {
            string sql = "";
            if (type == "乡科级正职核定数")
                sql = string.Format(@"UPDATE dbo.BianZhi SET TCMC = '',ZhengKeLingDaoTC = 0,ZhengKeLingDao = '' WHERE ZhengKeLingDaoTC = 1 and orgid  = {0}", orgid);
            else
                sql = string.Format(@"UPDATE dbo.BianZhi SET TCMC1 = '',ZhengKeFeiLingDaoTC = 0,ZhengKeFeiLingDao = '' WHERE ZhengKeFeiLingDaoTC = 1 and orgid  = {0}", orgid);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 查询实有人数等
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public ObservableCollection<BianZhiModel> GetBianZhiModelShiYou(int OrgID)
        {
            ObservableCollection<BianZhiModel> rights = new ObservableCollection<BianZhiModel>();
            string sql = string.Format(@"select o.Name OrgName,a.ZongBianShiYou,b.ZhengKeLingDaoShiYou,c.ZhengKeFeiLingDaoShiYou,
d.FuKeLingDaoShiYou,e.FuKeFeiLingDaoShiYou, f.KeYuanShiYou,g.WeiDingZhiShiYou,b1.ZongBianZhi,b1.ChuJiRenShu from OrgForHR o left join
(select case when COUNT(distinct ID) is null then 0 else COUNT(distinct ID) end ZongBianShiYou ,orgid OrgID from Emp_BasicInfo where empstatus = '在职' and orgID = {0} group by orgid) a on o.ID = a.OrgID left join
(select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end ZhengKeLingDaoShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '领导职务' and KeChuJi = 0  group by orgid) b on b.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end ZhengKeFeiLingDaoShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '乡科级正职（主任科员）' and DutyAttribute = '非领导职务' and KeChuJi = 0 group by orgid) c on c.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end FuKeLingDaoShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '领导职务' and KeChuJi = 0 group by orgid) d on d.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) = null then 0 else COUNT(distinct EmpbasicID) end FuKeFeiLingDaoShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '乡科级副职（副主任科员）' and DutyAttribute = '非领导职务' and KeChuJi = 0 group by orgid) e on e.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end KeYuanShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '科员'  and KeChuJi = 0 group by orgid) f on f.OrgID = o.ID left join
 (select case when COUNT(distinct EmpbasicID) is null then 0 else COUNT(distinct EmpbasicID) end WeiDingZhiShiYou,orgid OrgID from Emp_BasicInfo emp inner join Emp_Duty d on emp.ID = d.EmpbasicID
 where empstatus = '在职' and orgID = {0} and (JobStatus = '在任' or JobStatus = '兼任') and d.DutyLevel = '未定职'  and KeChuJi = 0 group by orgid) g on g.OrgID = o.ID   left join 
 (select (CONVERT(int, ISNULL(JiGuanXingZheng,0)) + CONVERT(int, ISNULL(ZhengFaXingZheng,0)) + 
CONVERT(int, ISNULL(CanZhaoGuanLi,0))) ZongBianZhi,
 [ID],[OrgID]
      ,[ZhengKeLingDao]
      ,[ZhengKeFeiLingDao]
      ,[FuKeLingDao]
      ,[FuKeFeiLingDao]
      ,[KeYuan]
      ,[WeiDingZhi]
      ,[ChuJiRenShu]
      ,[KeYiXiaRenShu]
      ,[JiGouMingCheng]
      ,[JiGuanXingZheng]
      ,[ZhengFaXingZheng]
      ,[CanZhaoGuanLi]
      ,[ZhengKeLingDaoTC]
      ,[ZhengKeFeiLingDaoTC]
      ,[TCMC]
      ,[TCMC1] from BianZhi where OrgID = {0}) b1 on b1.OrgID = o.ID where o.ID = {0}", OrgID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(BianZhiModel.FillEntity1(dr));
            }
            return rights;
        }
        #endregion

        #region 职务层次
        public ObservableCollection<ZhiJiModel> GetZhiJiModel(int ID)
        {
            ObservableCollection<ZhiJiModel> rights = new ObservableCollection<ZhiJiModel>();
            string sql = "";
            if (ID == 0)
                sql = string.Format(@"select * from zhiji");
            else
                sql = string.Format(@"select * from zhiji where ID = {0}", ID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhiJiModel.FillEntity(dr));
            }
            return rights;
        }
        public bool AddZhiJiModel(ZhiJiModel item)
        {
            string sql = string.Format(@"INSERT INTO ZhiJi
           ([DutyLevel]
           ,[JinE])
     VALUES
           ('{0}'
           ,'{1}')", item.DutyLevel, item.JinE);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool UpdateZhiJiModel(ZhiJiModel item)
        {
            string sql = string.Format(@"UPDATE ZhiJi
   SET [DutyLevel] = '{0}'
      ,[JinE] = '{1}'
 WHERE ID = {2}", item.DutyLevel, item.JinE, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        /// <summary>
        /// 导入数据前判断是否存在错误数据
        /// </summary>
        /// <param name="InEmpBasicModels"></param>
        /// <returns></returns>
        public bool CanDaoRuAll(ObservableCollection<Emp_BasicInfoModel> InEmpBasicModels)
        {
            if (InEmpBasicModels != null && InEmpBasicModels.Count > 0)
            {
                foreach (Emp_BasicInfoModel i in InEmpBasicModels)
                {
                    string sql = string.Format(@"select * from Emp_BasicInfo where Name = '{0}' and CardNo = '{1}'",
                                               i.Name, i.CardNo);
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        return false;
                    }
                }
                return true;
            }
            else return false;
        }
        /// <summary>
        /// 删除在导入过程中数据库中已经存在的重复数据
        /// </summary>
        /// <param name="InEmpBasicModels"></param>
        /// <returns></returns>
        public bool ShanChuDaoRuAll(ObservableCollection<Emp_BasicInfoModel> InEmpBasicModels)
        {
            int can = 0;
            if (InEmpBasicModels != null && InEmpBasicModels.Count > 0)
            {
                foreach (Emp_BasicInfoModel i in InEmpBasicModels)
                {
                    string sql = string.Format(@"delete from Emp_BasicInfo where Name = '{0}' and CardNo = '{1}'",
                                               i.Name, i.CardNo);
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        can = can;
                    else can = can + 1;
                }
                if (can == InEmpBasicModels.Count) return true;
                else return false;
            }
            else return false;
        }
        /// <summary>
        /// 导入数据(数据表导出和单位数据表导出)
        /// </summary>
        /// <param name="InEmpBasicModels"></param>
        /// <param name="InEmpEducation"></param>
        /// <param name="InEmpDegree"></param>
        /// <param name="InEmpResume"></param>
        /// <param name="InEmpDuty"></param>
        /// <param name="InEmpReward"></param>
        /// <param name="InEmpPunish"></param>
        /// <param name="InEmpAssenssment"></param>
        /// <param name="InEmpFamily"></param>
        public bool DaoRuAll(ObservableCollection<Emp_BasicInfoModel> InEmpBasicModels, ObservableCollection<Emp_EducationModel> InEmpEducation,
            ObservableCollection<Emp_DegreeModel> InEmpDegree, ObservableCollection<Emp_ResumeModel> InEmpResume,
            ObservableCollection<Emp_Duty> InEmpDuty, ObservableCollection<Emp_RewardInfoModel> InEmpReward,
            ObservableCollection<Emp_PunishmentModel> InEmpPunish, ObservableCollection<Emp_AssessmentModel> InEmpAssenssment,
            ObservableCollection<Emp_FamilyRelationModel> InEmpFamily)
        {
            if (InEmpBasicModels != null && InEmpBasicModels.Count > 0)
            {
                foreach (Emp_BasicInfoModel i in InEmpBasicModels)
                {
                    string sql = string.Format(@"INSERT INTO Emp_BasicInfo
           ([Name]
           ,[CardNo]
           ,[Gender]
           ,[BirthPlace]
           ,[HouseholdRegister]
           ,[Native]
           ,[Political]
           ,[Health]
           ,[WorkDate]
           ,[RegisterNo]
           ,[OrganizationType]
           ,[NowWorkDate]
           ,[NativePlace]
           ,[Marriage]
           ,[HomeAddress]
           ,[Phone]
           ,[EmpIdentity]
           ,[PartyTime]
           ,[Special]
           ,[TechnicalPositions]
           ,[PersonnelType]
           ,[RegisterDate]
            ,[Birthday]
,OrgID
        ,PositionName,TuiXiuDate,JuTuiXiuDate,HuoDeFangShi,EmpStatus)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}'
           ,'{20}'
           ,'{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}');SELECT @@IDENTITY ", i.Name, i.CardNo, i.Gender, i.BirthPlace, i.HouseholdRegister, i.Native, i.Political, i.Health,
                    i.WorkDate, i.RegisterNo, i.OrganizationType, i.NowWorkDate, i.NativePlace, i.Marriage, i.HomeAddress,
                    i.Phone, i.EmpIdentity, i.PartyTime, i.Special, i.TechnicalPositions, i.PersonnelType, i.RegisterDate, i.Birthday,
                    i.OrgID, i.PositionName, i.TuiXiuDate, i.JuTuiXiuDate, i.HuoDeFangShi, "在职");
                    DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                    int EmpID = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    #region 学历
                    if (InEmpEducation != null && InEmpEducation.Count > 0)
                    {
                        List<Emp_EducationModel> tem =
                            InEmpEducation.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_EducationModel edu in tem)
                            {
                                string edusql = string.Format(@"INSERT INTO Emp_Education
           ([EmpBasicID]
           ,[EducationName]
           ,[EducationType]
           ,[EducationaSystem]
           ,[GraduationDate]
           ,[School]
           ,[Professional]
           ,[IsTopEducation])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}')", EmpID, edu.EducationName, edu.EducationType, edu.EducationaSystem, edu.GraduationDate, edu.School, edu.Professional, edu.IsTopEducation);
                                AccessHelper.GetInstance().ExcuteDataSet(edusql);
                            }
                        }
                    }
                    #endregion
                    #region 学位
                    if (InEmpDegree != null && InEmpDegree.Count > 0)
                    {
                        List<Emp_DegreeModel> tem =
                            InEmpDegree.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_DegreeModel edu in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Degree
           ([EmpbasicID]
           ,[DegreeName]
           ,[LearningType]
           ,[EducationalSystem]
           ,[EducationDate]
           ,[School]
           ,[Professional])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, edu.DegreeName, edu.LearningType, edu.EducationalSystem, edu.EducationDate, edu.School, edu.Professional);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 简历
                    if (InEmpResume != null && InEmpResume.Count > 0)
                    {
                        List<Emp_ResumeModel> tem =
                            InEmpResume.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_ResumeModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Resume
           ([EmpbasicID]
           ,[BeginDate]
           ,[EndDate]
           ,[Unit]
           ,[Duty]
           ,[Witness]
           ,[IsPeriod])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, item.BeginDate, item.EndDate, item.Unit, item.Duty, item.Witness, item.IsPeriod);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 职务
                    if (InEmpDuty != null && InEmpDuty.Count > 0)
                    {
                        List<Emp_Duty> tem =
                            InEmpDuty.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_Duty item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Duty
           ([EmpbasicID]
           ,[DutyNo]
           ,[DutyName]
           ,[DutyLevel]
           ,[DutyAttribute]
           ,[OKDate]
           ,[OrganizationName]
           ,[Sec]
           ,[WorkCharge]
           ,[ServiceMode]
           ,[IsExceptionalPromotion]
           ,[JobStatus]
           ,[JobDate],RenQi,RZSYQ,LianRen,BRZDQRQ,RXRZWSXSJ)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}','{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}')", EmpID, item.DutyNo, item.DutyName, item.DutyLevel, item.DutyAttribute, item.OKDate, item.OrganizationName, item.Sec,
                    item.WorkCharge, item.ServiceMode, item.IsExceptionalPromotion, item.JobStatus, item.JobDate
                    , item.RenQi, item.RZSYQ, item.LianRen, item.BRZDQRQ, item.RXRZWSXSJ);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 奖励
                    if (InEmpReward != null && InEmpReward.Count > 0)
                    {
                        List<Emp_RewardInfoModel> tem =
                            InEmpReward.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_RewardInfoModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_RewardInfo
           ([EmpbasicID]
           ,[RewardNo]
           ,[RewardName]
           ,[RewardReason]
           ,[OtherRewardReason]
           ,[OKDate]
           ,[OKUnit])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, item.RewardNo, item.RewardName, item.RewardReason, item.OtherRewardReason, item.OKDate, item.OKUnit);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 处分
                    if (InEmpPunish != null && InEmpPunish.Count > 0)
                    {
                        List<Emp_PunishmentModel> tem =
                            InEmpPunish.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_PunishmentModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Punishment
           ([EmpbasicID]
           ,[PunishmentNo]
           ,[PunishmentName]
           ,[PunishmentReason]
           ,[OkUnit]
           ,[OKDate]
           ,[PunishmentTimeLimit])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, item.PunishmentNo, item.PunishmentName, item.PunishmentReason, item.OkUnit, item.OKDate, item.PunishmentTimeLimit);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 考核
                    if (InEmpAssenssment != null && InEmpAssenssment.Count > 0)
                    {
                        List<Emp_AssessmentModel> tem =
                            InEmpAssenssment.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_AssessmentModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_Assessment
           ([EmpbasicID]
           ,[AssessmentType]
           ,[AssessmentResult]
           ,[AssessmentDate])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}')", EmpID, item.AssessmentType, item.AssessmentResult, item.AssessmentDate);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                    #region 家庭
                    if (InEmpFamily != null && InEmpFamily.Count > 0)
                    {
                        List<Emp_FamilyRelationModel> tem =
                            InEmpFamily.Where(p => (p.EmpName == i.Name && p.EmpNo == i.CardNo)).ToList();
                        if (tem != null && tem.Count > 0)
                        {
                            foreach (Emp_FamilyRelationModel item in tem)
                            {
                                string degreesql = string.Format(@"INSERT INTO Emp_FamilyRelation
           ([EmpbasicID]
           ,[Name]
           ,[Appellation]
           ,[Unit]
           ,[Duty]
           ,[Political]
           ,[Birthday])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}')", EmpID, item.Name, item.Appellation, item.Unit, item.Duty, item.Political, item.Birthday);
                                AccessHelper.GetInstance().ExcuteDataSet(degreesql);
                            }
                        }
                    }
                    #endregion
                }
            }
            return true;
        }

        public PeiZhiLieModel GetLieByUserID(int userID)
        {
            ObservableCollection<PeiZhiLieModel> rights = new ObservableCollection<PeiZhiLieModel>();
            string sql = string.Format(@"select  top 1 * from peizhilie where userid  = {0} order by ID desc", userID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(PeiZhiLieModel.FillEntity(dr));
            }
            if (rights.Count > 0)
                return rights[0];
            else return null;
        }

        public bool UpdateLie(PeiZhiLieModel item)
        {
            string sql = "";
            if (item.ID == 0)
            {
                sql = string.Format(@"INSERT INTO PeiZhiLie
           ([UserID]
           ,[Content])
     VALUES
           ({0}
           ,'{1}')", item.UserID, item.Content);
            }
            else
            {
                sql = string.Format(@"UPDATE PeiZhiLie
   SET [UserID] = {0}
      ,[Content] = '{1}'
 WHERE ID = {2}", item.UserID, item.Content, item.ID);
            }

            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public HSSFWorkbook ExportExcelDW(List<BianZhiModel> list)
        {

            var hssWorkBook = new HSSFWorkbook();

            HSSFSheet sheet = hssWorkBook.CreateSheet("编制表");
            //sheet.SetColumnWidth(3, 50 * 256);  //设置列宽，50个字符宽度。宽度参数为1/256，故乘以256
            #region  *************
            ////一个标题样式
            //HSSFCellStyle titleStyle = hssWorkBook.CreateCellStyle();
            //titleStyle.BorderBottom = (HSSFCellStyle.BORDER_THIN);
            //titleStyle.BorderLeft = (HSSFCellStyle.BORDER_THIN);
            //titleStyle.BorderRight = (HSSFCellStyle.BORDER_THIN);
            //titleStyle.BorderTop = (HSSFCellStyle.BORDER_THIN);
            ////设置居中
            //titleStyle.Alignment = HSSFCellStyle.ALIGN_LEFT;
            //titleStyle.VerticalAlignment = HSSFCellStyle.ALIGN_LEFT;

            //HSSFFont font = hssWorkBook.CreateFont();
            //font.FontHeightInPoints = ((short)18);//设置字体大小
            //titleStyle.SetFont(font);

            //HSSFRow row0 = sheet.CreateRow(0);
            //HSSFCell head = row0.CreateCell(0);
            //head.CellStyle = titleStyle;
            //sheet.GetRow(0).Height = 30 * 20;
            //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 29));
            //head.SetCellValue("统计分析导出");
            #endregion

            #region   单元格样式
            HSSFCellStyle style = hssWorkBook.CreateCellStyle();
            //设置边框:
            style.BorderBottom = (HSSFCellStyle.BORDER_THIN);
            style.BorderLeft = (HSSFCellStyle.BORDER_THIN);
            style.BorderRight = (HSSFCellStyle.BORDER_THIN);
            style.BorderTop = (HSSFCellStyle.BORDER_THIN);


            //设置居中对齐
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            //垂直对齐
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;


            //设置背景色
            //style.FillBackgroundColor = HSSFColor.BLUE.index;// 设置背景色
            //style.FillForegroundColor = HSSFColor.BLUE.index;
            //style.FillPattern=(HSSFCellStyle.SOLID_FOREGROUND);

            //字体
            HSSFFont font = hssWorkBook.CreateFont();
            // font.FontName="黑体";
            font.FontHeightInPoints = ((short)12);//设置字体大小
            style.SetFont(font);


            // 设置自动换行
            style.WrapText = true;


            HSSFCellStyle style1 = hssWorkBook.CreateCellStyle();

            //设置居中对齐
            style1.Alignment = HSSFCellStyle.ALIGN_CENTER;
            //垂直对齐
            style1.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;


            //设置背景色
            //style.FillBackgroundColor = HSSFColor.BLUE.index;// 设置背景色
            //style.FillForegroundColor = HSSFColor.BLUE.index;
            //style.FillPattern=(HSSFCellStyle.SOLID_FOREGROUND);

            //字体
            HSSFFont font1 = hssWorkBook.CreateFont();
            font1.FontName="黑体";
            font1.FontHeightInPoints = ((short)20);//设置字体大小
            style1.SetFont(font1);


            // 设置自动换行
            style1.WrapText = true;
            #endregion
            //行
            short h = 0;
            for (int i = 0; i <=  2; i++)
            {
                HSSFRow row = sheet.CreateRow(i);
                if (i == 0)
                    h = 55 * 20;
                if (i == 1 )
                    h = 25 * 20;
                if (i == 2)
                    h = 40 * 20;
                sheet.GetRow(i).Height = h;

                //列
                for (int j = 1; j < 26; j++)
                {
                    HSSFCell cell = row.CreateCell(j);
                    cell.CellStyle = style;
                    if (j == 1)
                    {
                        sheet.SetColumnWidth(j, 20 * 256);//设置列宽，8个字符宽度。宽度参数为1/256，故乘以256
                    }
                    if (j == 3)
                    {
                        sheet.SetColumnWidth(j, 14 * 256);//设置列宽，8个字符宽度。宽度参数为1/256，故乘以256
                    }
                    else sheet.SetColumnWidth(j, 12 * 256);
                    if (i == 0)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 25));
                        cell.CellStyle = style1;
                        cell.SetCellValue(list[0].OrgName + "编制信息表");
                    }

                    #region   row 1
                    if (i == 1)
                    {

                        if (j == 1)
                        {
                            //sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2));
                            cell.SetCellValue("科室名称");
                        }
                        if (j == 2)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 2, 7));
                            cell.SetCellValue("正科级");
                        }
                        if (j == 8)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 8, 14));
                            cell.SetCellValue("副科级");
                        }
                        if (j == 15)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 15, 19));
                            cell.SetCellValue("主任科员");
                        }
                        if (j == 20)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 20, 24));
                            cell.SetCellValue("副主任科员");
                        }
                        if (j == 25)
                        {
                            cell.SetCellValue("科员");
                        }
                        
                    }
                    #endregion
                    #region  row 2
                    if (i == 2)
                    {

                        if (j == 1)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
                            cell.SetCellValue("科室名称");
                        }
                        if (j == 2)
                        {
                            cell.SetCellValue("正科级职数");
                        }
                        if (j == 3)
                        {
                            cell.SetCellValue("正科级实有人数");
                        }
                        if (j == 4)
                        {
                            cell.SetCellValue("兼任占用");
                        }
                        if (j == 5)
                        {
                            cell.SetCellValue("借用情况");
                        }
                        if (j == 6)
                        {
                            cell.SetCellValue("过渡性超职数");
                        }
                        if (j == 7)
                        {
                            cell.SetCellValue("正科级可用职数");
                        }
                        if (j == 8)
                        {
                            cell.SetCellValue("副科级职数");
                        }
                        if (j == 9)
                        {
                            cell.SetCellValue("副科级实有人数");
                        }
                        if (j == 10)
                        {
                            cell.SetCellValue("兼任占用");
                        }
                        if (j == 11)
                        {
                            cell.SetCellValue("借用情况");
                        }
                        if (j == 12)
                        {
                            cell.SetCellValue("是否为同科室借用");
                        }
                        if (j == 13)
                        {
                            cell.SetCellValue("过渡性超职数");
                        }
                        if (j == 14)
                        {
                            cell.SetCellValue("副科级可用职数");
                        }
                        if (j == 15)
                        {
                            cell.SetCellValue("主任科员职数");
                        }
                        if (j == 16)
                        {
                            cell.SetCellValue("主任科员实有人数");
                        }
                        if (j == 17)
                        {
                            cell.SetCellValue("借用情况");
                        }
                        if (j == 18)
                        {
                            cell.SetCellValue("过渡性超职数");
                        }
                        if (j == 19)
                        {
                            cell.SetCellValue("主任科员可用职数");
                        }
                        if (j == 20)
                        {
                            cell.SetCellValue("副主任科员职数");
                        }
                        if (j == 21)
                        {
                            cell.SetCellValue("副主任科员实有人数");
                        }
                        if (j == 22)
                        {
                            cell.SetCellValue("借用情况");
                        }
                        if (j == 23)
                        {
                            cell.SetCellValue("过渡性超职数");
                        }
                        if (j == 24)
                        {
                            cell.SetCellValue("副主任科员可用职数");
                        }
                        if (j == 25)
                        {
                            cell.SetCellValue("科员实有人数");
                        }
                        

                    }
                    #endregion
                }
            }
            for (int i = 3; i < list.Count + 3; i++)
            {
                HSSFRow row = sheet.CreateRow(i);
                //if (i == 1 || i == 2)
                //    h = 25 * 20;
                //sheet.GetRow(i).Height = h;

                //列
                for (int j = 1; j < 26; j++)
                {
                    HSSFCell cell = row.CreateCell(j);
                    cell.CellStyle = style;
                    if (j == 1)
                    {
                        sheet.SetColumnWidth(j, 20 * 256);//设置列宽，8个字符宽度。宽度参数为1/256，故乘以256
                    }
                    if (j == 3)
                    {
                        sheet.SetColumnWidth(j, 14 * 256);//设置列宽，8个字符宽度。宽度参数为1/256，故乘以256
                    }
                    else sheet.SetColumnWidth(j, 12 * 256);
                    if (j == 1)
                    {
                        cell.SetCellValue(list[i-3].JiGouMingCheng);
                    }
                    if (j == 2)
                    {
                        cell.SetCellValue(list[i-3].ZhengKeLingDaoString);
                    }
                    if (j == 3)
                    {
                        cell.SetCellValue(list[i - 3].zhengkeshiyou);
                    }
                    if (j == 4)
                    {
                        cell.SetCellValue(list[i - 3].zhengkejianren);
                    }
                    if (j == 5)
                    {
                        cell.SetCellValue(list[i - 3].ZhengKeJieYongQingKuang);
                    }
                    if (j == 6)
                    {
                        cell.SetCellValue(list[i - 3].ZhengKeChaoZhiQingKuang);
                    }
                    if (j == 7)
                    {
                        cell.SetCellValue(list[i - 3].ZhengKeShiJi);
                    }
                    if (j == 8)
                    {
                        cell.SetCellValue(list[i - 3].ZhengKeFeiLingDaoString);
                    }
                    if (j == 9)
                    {
                        cell.SetCellValue(list[i - 3].fukeshiyou);
                    }
                    if (j == 10)
                    {
                        cell.SetCellValue(list[i - 3].fukejianren);
                    }
                    if (j == 11)
                    {
                        cell.SetCellValue(list[i - 3].FuKeJieYongQingKuang);
                    }
                    if (j == 12)
                    {
                        cell.SetCellValue(list[i - 3].IsZhengKeTongKeShiJieYongS);
                    }
                    if (j == 13)
                    {
                        cell.SetCellValue(list[i - 3].FuKeChaoZhiQingKuang);
                    }
                    if (j == 14)
                    {
                        cell.SetCellValue(list[i - 3].FuKeShiJi);
                    }
                    if (j == 15)
                    {
                        cell.SetCellValue(list[i - 3].FuKeLingDaoS);
                    }
                    if (j == 16)
                    {
                        cell.SetCellValue(list[i - 3].zhurenshiyou);
                    }
                    if (j == 17)
                    {
                        cell.SetCellValue(list[i - 3].ZhuRenJieYongQingKuang);
                    }
                    if (j == 18)
                    {
                        cell.SetCellValue(list[i - 3].ZhuRenChaoZhiQingKuang);
                    }
                    if (j == 19)
                    {
                        cell.SetCellValue(list[i - 3].ZhuRenShiJi);
                    }
                    if (j == 20)
                    {
                        cell.SetCellValue(list[i - 3].FuKeFeiLingDaoS);
                    }
                    if (j == 21)
                    {
                        cell.SetCellValue(list[i - 3].fuzhurenshiyou);
                    }
                    if (j == 22)
                    {
                        cell.SetCellValue(list[i - 3].FuZhuRenJieYongQingKuang);
                    }
                    if (j == 23)
                    {
                        cell.SetCellValue(list[i - 3].FuZhuRenChaoZhiQingKuang);
                    }
                    if (j == 24)
                    {
                        cell.SetCellValue(list[i - 3].FuZhuRenShiJi);
                    }
                    if (j == 25)
                    {
                        cell.SetCellValue(list[i - 3].keyuancount);
                    }
                }
            }
            return hssWorkBook;
        }

        public ObservableCollection<Emp_BasicInfoModel> GetEmp(string con)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql = string.Format("",con );
         
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;

        }
    }
}
